Error in dynamic statement PL/PGSQL (functions and operators can take at most one set argument)

Question

I am trying to execute the code below. I need this because, i wanted to pass the table names as parameters (character varying):

EXECUTE 'CREATE TABLE '||tmp_table||' AS SELECT study,
'||unnest(columns)||' AS variable_id, '||unnest_table(tblName)||
' AS variable_value FROM '||tblName;

However, I run into the error below:

functions and operators can take at most one set argument

Any ideas on how to solve this?

Here is the code for the unnest_table function:

CREATE OR REPLACE FUNCTION unnest_table(_tbl text)
RETURNS SETOF text LANGUAGE plpgsql AS $func$
BEGIN
    RETURN QUERY EXECUTE '
     SELECT unnest(ARRAY[' || (
SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
FROM   pg_catalog.pg_attribute a 
WHERE  a.attrelid = _tbl::regclass
AND    a.attnum > 0
AND    a.attisdropped = false
) || '])
FROM   ' || _tbl::regclass;

END
$func$;

`unnest_table` is not a normal built-in function. How is it defined? Are you trying to do something like a `string_agg` ? — Craig Ringer, Apr 07 '14 at 04:43
I edited the question to include the definition of the unnest_table function. — jackeblagare, Apr 07 '14 at 05:19
That's an incredibly bizarre way to write such a function. You create an array literal using `string_agg` then `unnest` it? *boggle* ? — Craig Ringer, Apr 07 '14 at 05:23
It's an important step of unpivoting. The problem isn't there though since I already tested that although with hard-coded table names. — jackeblagare, Apr 07 '14 at 05:35
Your code is *a complete mess*. Please start by describing what you want to achieve, add an example and what the result should be. To top it off, provide your version of Postgres. — Erwin Brandstetter, Apr 07 '14 at 23:40

pozs · Answer 1 · 2014-04-07T09:55:13.927

That's because unnest & your unnest_table both returns SETOF <sometype>, and operators can take at most one set argument, so f.ex.:

SELECT unnest(ARRAY['a', 'b', 'c']);

-- will return

unnest
------
"a"
"b"
"c"


SELECT unnest(ARRAY['a', 'b', 'c']) || 'd';

-- will return

?column?
--------
"ad"
"bd"
"cd"


SELECT unnest(ARRAY['a', 'b', 'c']) || 'd' || unnest(ARRAY['a', 'b', 'c']);

-- will return

ERROR: functions and operators can take at most one set argument
SQL state: 0A000

Edit: but i highly doubt, you want to create that much table with the same name - also EXECUTE does not accept more than one row:

ERROR: query "..." returned more than one row
SQL state: 21000

I think you should use something like the array_to_string() function.

Error in dynamic statement PL/PGSQL (functions and operators can take at most one set argument)

1 Answers1

Linked