How do I unnest an array inside a function?

Question

create or replace function pd.check(
    interval_ text[]) 

returns void as

$BODY$
BEGIN

EXECUTE '
drop table if exists check_;
create temp table check_ 
as
(
    select unnest(' || interval_ || ')
) ;
';
END;
$BODY$
LANGUAGE PLPGSQL volatile;

I am running it as

select pd.check(ARRAY['2','3','4']);

It gives me an error :

operator is not unique: unknown || text[]

HINT: Could not choose a best candidate operator. You may need to add explicit type casts.

Answer 1

Assuming current Postgres 9.6, your function would work like this:

CREATE OR REPLACE FUNCTION pd.check(interval_ text[]) 
  RETURNS void AS
$func$
BEGIN
   EXECUTE '
      DROP TABLE IF EXISTS pg_temp.check_;
      CREATE TEMP TABLE check_  AS
      SELECT unnest($1)'
   USING $1;                                -- pass as value
END
$func$  LANGUAGE plpgsql;

Notes

• You could concatenate the parameter value as string, but then you need an explicit type cast (since concatenating an untyped string literal and text[] is ambiguous to Postgres, it might produce text or text[], hence the error!) and escape special characters to make it work. quote_literal() does both: quote_literal(interval_). Still, don't. Instead, pass the parameter as value, not as string. That's faster and safer and avoids any such error as you show.
  Note that $1 in the command string refers to the first expression provided by the USING clause, not to the function parameter. The 2nd instance of $1 actually refers to the function parameter (different scope!).

• Note, that this superior way of passing values to DML statements works here because it's part of the included SELECT statement, but not for other utility commands. Details:

  • “ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

• drop table check_; in a function is dangerous. Obviously you want to target the temporary table. But if that should not exist, the next available table of the same name in the search_path would be dropped. Potentially catastrophic damage. To target the temp table and no other, schema-qualify with the pseudo-name pg_temp.

• CREATE TABLE AS does not require parentheses around a following SELECT command.

• VOLATILE is the default (and correct for this function), I omitted the noise.

In this particular case you would not need dynamic SQL. See @klin's answer. But EXECUTE is still a good choice for queries that have nothing to gain from plan caching.

Answer 2

You do not need a dynamic SQL (execute):

create or replace function pd.check(interval_ text[]) 
returns void as
$body$
begin
    drop table if exists check_;
    create temp table check_
    as select unnest(interval_);
end;
$body$
language plpgsql volatile;

Test:

select pd.check(array['2','3','4']);
select * from check_;

 unnest 
--------
 2
 3
 4
(3 rows)    

Answer 3

This is similar to the other answer, but you really shouldn't be using RETURNS VOID; you should be returning a set from the function itself.

CREATE OR REPLACE FUNCTION pd.check(
  intervals_ TEXT[] )
RETURNS TABLE ( pd_interval TEXT )
$f$
BEGIN
  RETURN QUERY SELECT UNNEST(intervals_);
END;
$f$ LANGUAGE plpgsql IMMUTABLE;

postgres=# select * from pd_check(ARRAY['1','2','3']);
 pd_interval 
-------------
 1
 2
 3
(3 rows)

Other changes I made:

1. You shouldn't name a column after a different data type than the column itself, hence changing interval_ to intervals_.
2. The function is actually IMMUTABLE, not VOLATILE, as-is.