PostgreSQL: Iterating over array of text and executing SQL

Question

I am copying tables from one schema to another. I am trying to pass argument of name of tables that I want to copy. But no table is created in Schema when I execute the CALL.

Command: CALL copy_table('firstname', 'tableName1,tableName2,tableName3');

CREATE OR REPLACE PROCEDURE copy_table(user VARCHAR(50), strs TEXT)
LANGUAGE PLPGSQL
AS $$
DECLARE
    my_array TEXT;
BEGIN
    FOR my_array IN
        SELECT string_to_array(strs, ',')
    LOOP
        EXECUTE 'CREATE TABLE ' || user || '.' || my_array || ' (LIKE public.' || my_array || ' INCLUDING ALL)';
    END LOOP;
$$

Could you please help? Thank you.

score 0 · Accepted Answer · answered May 27 '22 at 13:55

The function string_to_array returns an array value. Looping through arrays is performed by FOREACH command, not FOR.

See documentation:

https://www.postgresql.org/docs/14/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

score 0 · Answer 2 · answered May 29 '22 at 23:04

Loop over an array with FOREACH like Simon suggested. Or with FOR in old (or any) versions. See:

Iterating over integer[] in PL/pgSQL

Typically, a set-based solution is shorter and faster, though:

CREATE OR REPLACE PROCEDURE copy_tables(_schema text, VARIADIC _tables text[])
  LANGUAGE plpgsql AS
$proc$
BEGIN
   EXECUTE
   (SELECT string_agg(format('CREATE TABLE %1$I.%2$I (LIKE public.%2$I INCLUDING ALL)', _schema, t), E';\n')
    FROM   unnest(_tables) t);
END
$proc$;

About VARIADIC:

Return rows matching elements of input array in plpgsql function

Call, passing list of table names:

CALL copy_tables('firstname', 'tableName1', 'tableName2', 'tableName3');

Or, passing genuine array:

CALL copy_tables('foo', VARIADIC '{tableName1,tableName2,tableName3}');

Or, passing (and converting) comma-separated string (your original input):

CALL copy_tables('foo', VARIADIC string_to_array('tableName1,tableName2,tableName3', ','));

I use format() to concatenate the SQL string safely. Note that identifiers must be passed as case-sensitive strings! See:

PostgreSQL: Iterating over array of text and executing SQL

2 Answers2