How to call a PostgreSQL 9.1 function?

Question

How to call a PostgreSQL 9.1 function?

Navigation

#1 by (3 votes)

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 is giving error:

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 finding that the way I'm calling the function is not entirely sure.

postgresql

asked by anonymous 31.01.2014 / 15:06

1 answer

Error when deploying on Wildfly Consume webservice dynamically

score 3 · Answer 1

You have 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.

Try traditional syntax (never fails 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 does not understand, then you include the cast ... In case you even 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 ...

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[]);