How to call a Postgresql 9.1 function?

Asked

Viewed 5,437 times

3

I have a function:

CREATE OR REPLACE FUNCTION f_2(p_vetoratributos text[], p_vetorvalores numeric[])

I’m trying to call:

SELECT  f_2('{"0","0","0"}', '{0,1,2,3,4,5,6,7,8,9,10,11,12,13}')

But he’s making a mistake:

ERRO:  operador não existe: text >= integer
LINE 1: SELECT fosforo>=8  AND  kalcio>=9
^
HINT:  Nenhum operador corresponde com o nome e o(s) tipo(s) de argumento(s) informados.
Você precisa adicionar conversões de tipo explícitas.
QUERY:  SELECT fosforo>=8  AND  kalcio>=9
CONTEXT:  PL/pgSQL function "f_2" line 14 at IF

I’m thinking that the way I’m calling the function isn’t quite right.

postgresql

  • By error message you need to convert type textfor integer, can do this with: cast(campo as tipo).

1 answer

3

You expressed two strings in the call. If you do 

 SELECT  '{"0","0","0"}', '{0,1,2,3,4,5,6,7,8,9,10,11,12,13}';

or

 SELECT  '{"0","0","0"}'||'ola', '{0,1,2,3,4,5,6,7,8,9,10,11,12,13}'||'ola';

will notice that it’s really all string.

Experiment with traditional syntax (never flawed or ambiguous),

   SELECT  array['0','0','0'], array[0,1,2,3,4,5,6,7,8,9,10,11,12,13];

here my Postgresql (pg) understood as text[] and int[]. If your pg version doesn’t understand, then you include the cast... In case even you want cast for type numeric,

   SELECT  array['0','0','0']::text[], 
           array[0,1,2,3,4,5,6,7,8,9,10,11,12,13]::numeric[];

That is to say...

Solution

 SELECT  f_2(array['0','0','0']::text[], array[0,1,2,3,4,5,6,7,8,9,10,11,12,13]::numeric[]);

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.