Array error passing dynamic number of parameters to function

Question

I'm trying to create a function to receive the name of the table in my schema already created and a several name of columns within this table (dynamic number of columns) and return a table with all the columns in a unique column with the value of each column separated by comma.

I'm trying this:

CREATE OR REPLACE PROCEDURE public.matching(IN table text, VARIADIC column_names text[])
    LANGUAGE 'plpgsql'

AS $BODY$DECLARE  
    column_text text;
BEGIN
EXECUTE format ($$ SELECT array_to_string(%s, ' ')$$, column_names) into column_text;

EXECUTE format ($$ CREATE TABLE temp1 AS
SELECT concat(%s, ' ') FROM %s $$, column_text, table);

END;$BODY$;

This return an error: ERROR: syntax error at or near «{» LINE 1: SELECT array_to_string({city,address}, ' ')

which is the error?

Answer 1

If you simplify the generation of the dynamic SQL, things get easier:

CREATE OR REPLACE PROCEDURE public.matching(IN table_name text, VARIADIC column_names text[])
    LANGUAGE plpgsql
AS 
$BODY$
DECLARE  
  l_sql text;
BEGIN
  l_sql := format($s$
                   create table temp1 as
                   select concat_ws(',', %s) as everything 
                   from %I
                  $s$, array_to_string(column_names, ','), table_name);
  raise notice 'Running %', l_sql;
  EXECUTE l_sql;
END;
$BODY$;

So if you e.g. pass in 'some_table' and {'one', 'two', 'three'} the generated SQL will look like this:

create table temp1 as select concat_ws(',', one,two,three) as everything from some_table

I also used a column alias for the new column, so that the new table has a defined name. Note that the way I put the column names into the SQL string won't properly deal with identifiers that need double quotes (but they should be avoided anyway)

---

If you want to "return a table", then maybe a function might be the better solution:

CREATE OR REPLACE function matching(IN table_name text, VARIADIC column_names text[])
  returns table (everything text)
  LANGUAGE plpgsql
AS 
$BODY$
DECLARE  
  l_sql text;
BEGIN
  l_sql := format($s$
                   select concat_ws(',', %s) as everything 
                   from %I
                  $s$, array_to_string(column_names, ','), table_name);
  return query execute l_sql;
END;
$BODY$;

Then you can use it like this:

select *
from matching('some_table', 'one', 'two', 'three');