CODEWITHSUNDEEP
postgresqlfunctionfor-loopplpgsqlpsql
588chm
588chm

Reputation: 227

Create table with names from rows in a table

I have a table with one text column. I would like to create a function that creates tables named after the rows of the original table. (Then fill up the table with stuff which i not relevant now.) Based on the examples I've found here, I made this:

CREATE OR REPLACE FUNCTION public.create_tables(t_name character varying)
RETURNS void AS
$BODY$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT table_names FROM format('%I',t_name)
    LOOP
        -- create table with the name as the current row of table_names
        -- do something
        -- in this case I used raise notice to see if it's working
        RAISE NOTICE '%', rec.table_names;
    END LOOP;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE

For this I got an error saying "column table_names does not exist" even though this is the name of the column in my table containing the table names I'd like to create. Since this is the only column, I've tried "SELECT *" as well, but it doesn't work

I've also tried this:

CREATE OR REPLACE FUNCTION public.create_tables(t_name character varying)
RETURNS void AS
$BODY$
DECLARE 
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM format('%I',t_name)
    LOOP
        RAISE NOTICE '%', rec;
    END LOOP;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE

As a result I got back the t_name argument of the function. I've also tried directly calling the table:

CREATE OR REPLACE FUNCTION public.create_tables()
RETURNS void AS
$BODY$
DECLARE 
    rec RECORD;
BEGIN
    FOR rec IN SELECT table_names FROM mytable
    LOOP
        RAISE NOTICE '%', rec;
    END LOOP;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE

It works and lists all rows very vell, but on the future I'd like to use this function with several times, on different tables, so it would be important to have an argument for a table name in the function.

Upvotes: 0

Views: 298

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

The simplest way is to use psql's \gexec:

SELECT format(
          'CREATE TABLE %I (id integer PRIMARY KEY);',
          t_name
       )
FROM table_names \gexec

But you can also loop through the query results in a PL/pgSQL loop and EXECUTE the resulting statement:

DO $$DECLARE
   sql text;
BEGIN
   FOR sql IN
      SELECT format(
                'CREATE TABLE %I (id integer PRIMARY KEY);',
                t_name
             )
      FROM table_names
   LOOP
      EXECUTE sql;
   END LOOP;
END;$$;

Upvotes: 1

Related Questions