Postgresql function to create table

Question

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this:

CREATE OR REPLACE FUNCTION create_table_type1(t_name VARCHAR(30)) RETURNS VOID AS $$
BEGIN
    EXECUTE "CREATE TABLE IF NOT EXISTS t_"|| t_name ||"
    (
    id SERIAL,
    customerid INT,
    daterecorded DATE,
            value DOUBLE PRECISION,
    PRIMARY KEY (id)
    )"
END
$$ LANGUAGE plpgsql

Then call it like:

SELECT create_table_type1('one');

Is it possible?

Erwin Brandstetter · Accepted Answer · 2021-02-20T10:37:56.147

Answer is yes. :)

CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
  RETURNS VOID
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('
      CREATE TABLE IF NOT EXISTS %I (
       id serial PRIMARY KEY,
       customerid int,
       daterecorded date,
       value double precision
      )', 't_' || t_name);
END
$func$;

I am using format() with %I to sanitize the table name and avoid SQL injection. Requires PostgreSQL 9.1 or above.

Be sure to use single quotes ('') for data. Double quotes ("") are for identifiers in SQL.

score 2 · Answer 2 · answered Oct 31 '14 at 14:42

yes, this is possible. however, you have to be a little careful. DDLs in a stored procedure USUALLY work. in some nasty corner cases you might end up with "cache lookup" errors. The reason is that a procedure is basically a part of a statement and modifying those system objects on the fly can in rare corner cases cause mistakes (has to be). This cannot happen with CREATE TABLE, however. So, you should be safe.

Postgresql function to create table

2 Answers2

Linked