PostgreSQL: Iterating over array of text and executing SQL-CodePudding

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.

CodePudding user response：

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;