CODEWITHSUNDEEP
postgresqlplpgsql
Kabi
Kabi

Reputation: 2045

Creating a dynamic Table with Plpgsql

I have a scenario, which is a little bit complex. I want to create a table, which its name and fields are defined by users. It's not very difficualt to set the name of the table, but a table could have 2 field and another 5 with the different names and so on. I want to do this job with pl/pgsql. Is it generally possible to do that in pl/pgsql? Is it possible to read the number of field at the first and then for each one read the name of the field? could you please give me some hints?

Upvotes: 0

Views: 1430

Answers (1)

Szymon Lipiński
Szymon Lipiński

Reputation: 28574

With the function like this:

CREATE OR REPLACE FUNCTION mktable(tabname TEXT, fields TEXT[]) 
RETURNS VOID AS $$
DECLARE 
  q TEXT := ''; 
BEGIN
  q := 'CREATE TABLE ' || quote_ident(tabname) || ' ( ';
  for i in array_lower(fields, 1) .. array_upper(fields, 1) loop
    q := q || quote_ident(fields[i]) || ' TEXT ';
    if i <> array_upper(fields, 1) then
      q := q || ',';
    end if; 
  end loop;
  q := q || ' ) ';
  raise notice 'Running query: ';
  raise notice '%', q;
  execute q;
END;
$$ LANGUAGE PLPGSQL;

You can run it like:

SELECT mktable('tab1', ARRAY['a', 'b', 'ccc', 'd d']);

and then you have the table:

    Table "public.tab1"
 Column | Type | Modifiers 
--------+------+-----------
 a      | text | 
 b      | text | 
 ccc    | text | 
 d d    | text |

Upvotes: 1

Related Questions