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?

fl0cke · Accepted Answer · 2016-02-25 17:56:40Z

4

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).

edited Feb 25, 2016 at 17:56

answered Feb 25, 2016 at 16:42

fl0cke

2,9041 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jesús López · Accepted Answer · 2016-02-25 17:20:38Z

1

Given the following function:

CREATE OR REPLACE FUNCTION get_item_ids(items text[]) RETURNS integer[] AS
$BODY$
BEGIN
    RETURN ARRAY(SELECT id FROM b WHERE item_id = ANY (items));
END
$BODY$
LANGUAGE plpgsql VOLATILE;

The following query:

SELECT get_item_ids('{"A",  "B"}'::text[])

Shows this:

{1,2,7}

On the other hand, the function:

CREATE TYPE id_record AS (id integer);

CREATE OR REPLACE FUNCTION get_item_ids2(text[]) RETURNS SETOF id_record AS
$BODY$
 SELECT id FROM b WHERE item_id = ANY ($1);
$BODY$
LANGUAGE SQL VOLATILE;

The query:

SELECT id FROM get_item_ids2('{"A",  "B"}'::text[])

Gives you:

+----+
| Id |
+----+
|  1 |
|  2 |
|  7 |
+----+

edited Feb 25, 2016 at 17:20

answered Feb 25, 2016 at 16:48

Jesús López

9,3489 gold badges47 silver badges83 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related