PostgreSQL - array value must start with "{" or dimension information

Question

I’m trying to create a function which will filter based on ANY in a supplied text array. The function is raising the following: “ERROR: array value must start with "{" or dimension information” when attempting to execute a query within a pgplsql function. The query works fine when the exact SQL statement is run in a query window.

The following illustrates the issue with a simplified sample.

Given the table definition:

CREATE TABLE b
(
    id serial NOT NULL,
    item_id character varying(2) NOT NULL,
    CONSTRAINT b_pkey PRIMARY KEY (id)
);

And sample data:

id  item_id
1      A
2      B
3      D
4      T
5      G
6      T
7      B

And function:

CREATE OR REPLACE FUNCTION get_item_ids() RETURNS integer[] AS
$BODY$DECLARE
    qry text;
    ids integer[];
    items text[];
BEGIN
    items := ARRAY['A','B','C']::text[];
    qry := format('SELECT id FROM b WHERE item_id = ANY(%L)', items);
    raise notice '%', qry;
    execute qry into ids;
    raise notice 'ids:%', ids;
    return ids;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

When the function is run the following output is generated:

NOTICE:  SELECT id FROM b WHERE item_id = ANY('{A,B,C}')

ERROR:  array value must start with "{" or dimension information
CONTEXT:  PL/pgSQL function get_item_ids() line 9 at EXECUTE statement

********** Error **********
ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: PL/pgSQL function get_item_ids() line 9 at EXECUTE statement

Copying and pasting the "SELECT id FROM b WHERE item_id = ANY('{A,B,C}')" statement into a Query windows yields the following expected results:

id integer
   1
   7
   2

I have tried many other options, including type casting, different quoting, and using the array_to_string and unnest functions. Can anyone explain what's happening and suggest an alternative which would work?

Answer 1

Try this statement inside your function:

EXECUTE 'SELECT ARRAY(SELECT id FROM b WHERE item_id = ANY($1))' INTO ids USING items;

• Use USING to safely execute a dynamic statement. This method is preferable to inserting data values into the command string as text (with format).
• You need to wrap the returned rows with the ARRAY constructor to produce an integer[] (or refactor your function to return a SETOF integer if that's what you actually want).