Smart Softwares - D. & S. escreveu:
Hello to all... I am with a problem in a function that I am creating.
The parameters that are function go to receive are an ARRAY and the
return also will be an ARRAY, the entrance parameters will be used in
a clause SQL. E the exit ARRAY will be the result of the SQL. However,
I elaborated the function and independent of the value that played in
the ARRAY the result of the SQL was always null. To make a test I
elaborated the function below and I perceived that my ARRAY had the
null values.
CREATE OR REPLACE FUNCTION TB_ADM_CEP_SEL (numeric [])
RETURNS numeric [] AS
$body$
DECLARE
BEGIN
--SELECT "TX_CEP" FROM "SMART"."TB_ADM_CEP"
--WHERE "PK_IN_COD_CEP" = $1[0];
RETURN $1[0];
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Somebody knows what I am making of made a mistake in this function to
have these results? They forgive my English good and not very obliged
for the suggestions.
Thank you...
Hi,
your problem happened because the array type are zero based, the array
type iniciate your index with 1.
try use RETURN $1[1] and you will see the correct answer.
ok.
--
Ivo Nascimento
Iann tech - Desenvolvendo soluções com performance e segurança
http://www.ianntech.com.br
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
