ERROR: op ANY/ALL (array) requires array on right side in Postgres Function

Question

I am getting error in my PostgreSQL function where I am filtering my data by passing two parameters.Below is my Function and table structure please tell me where and what I am doing wrong

CREATE TABLE table_2(
  id_col text,
  name_col text);

INSERT INTO table_2(id_col, name_col)
VALUES (1, 'A'),(2, 'B'),(3, 'C'),
       (4, 'D'),(5, 'E'),(6, 'F');

CREATE OR REPLACE FUNCTION test_str_1(IN param_name_col text,
                           VARIADIC integer[], OUT id_col text, OUT name_col text)
  RETURNS SETOF record AS
$BODY$
  BEGIN
return query
   SELECT t2.id_col,t2.name_col from table_2 t2
   Where t2.name_col = param_name_col AND t2.id_col::int = ANY($1) ;
  END
$BODY$
  LANGUAGE plpgsql VOLATILE

SELECT test_str_1('A', 1,2,3);

Why would you declare t2.id_col to be text if you enter only integer values and depend on a cast to integer anyway? Doesn't make sense. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 15, 2016 at 17:41

Lukasz Szozda · Accepted Answer · 2016-01-14 10:15:27Z

You can use unnest:

CREATE OR REPLACE FUNCTION test_str_1(IN param_name_col text,
                           VARIADIC integer[], OUT id_col text, OUT name_col text)
RETURNS SETOF record AS
$BODY$
BEGIN
   return query
   SELECT t2.id_col,t2.name_col
   from table_2 t2
   Where t2.name_col = param_name_col 
      AND t2.id_col::int IN (SELECT * FROM unnest($2));
END
$BODY$
LANGUAGE plpgsql VOLATILE

SELECT test_str_1('A', 1,2,3);
-- "(1,A)"

or just change ANY($1) to ANY($2) ($1 - is first argument, $2 - is second):

CREATE OR REPLACE FUNCTION test_str_1(IN param_name_col text,
                              VARIADIC integer[], OUT id_col text, OUT name_col text)
RETURNS SETOF record AS
$BODY$
BEGIN
   return query
   SELECT t2.id_col,t2.name_col
   from table_2 t2
   Where t2.name_col = param_name_col 
      AND t2.id_col::int = ANY($2);
END
$BODY$
LANGUAGE plpgsql VOLATILE

SELECT test_str_1('A', 1,2,3);
-- "(1,A)"

$1 is first parameter with value 'A' and ANY needs argument of type array ($2).

Collectives™ on Stack Overflow

ERROR: op ANY/ALL (array) requires array on right side in Postgres Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related