title: PostgreSQL - array value must start with "{" or dimension information
body: <p>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.</p>
  
  <p>The following illustrates the issue with a simplified sample.</p>
  
  <p>Given the table definition:  </p>
  
  <pre><code>CREATE TABLE b
  (
      id serial NOT NULL,
      item_id character varying(2) NOT NULL,
      CONSTRAINT b_pkey PRIMARY KEY (id)
  );
  </code></pre>
  
  <p>And sample data:</p>
  
  <pre><code>id  item_id
  1      A
  2      B
  3      D
  4      T
  5      G
  6      T
  7      B
  </code></pre>
  
  <p>And function:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>When the function is run the following output is generated:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>id integer
     1
     7
     2
  </code></pre>
  
  <p>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?</p>