Обсуждение: Function with Array
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;
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
select "SMART"."TB_ADM_CEP_SEL1"('{4,3}')
Mas dá o seguinte erro:
ERROR: array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function "TB_ADM_CEP_SEL1" while casting return value to function's return type
Já se eu modifico para restornar o $1[0] não dá erro, mas retorna nulo.
Obrigado!
I tried its suggestion... I modified the function to return the first position from the ARRAY ($1[1 ]), but now the function returns an error. To execute the function I type the following command:
select "SMART"."TB_ADM_CEP_SEL1"('{4,3}')
But it gives the following error:
ERROR: array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function "TB_ADM_CEP_SEL1" while casting return value to function's return type
Already if I modify to restornar $1[0 ] I do not give error, but he returns null.
Thank you!
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