Hi Ivo,
Tentei a sua sugestão... alterei a função para retornar a primeira posição do ARRAY ($1[1]), mas agora a função retorna um erro.
Para executar a função digito o seguinte comando:
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!
2007/1/23, Iannsp <iannsp@gmail.com>: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