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?

Answer 1

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.

• Alan Cor: Thank you again, it works but you've got a little mistake. It should be 'CREATE TABLE IF NOT EXISTS %I', right?
• Erwin Brandstetter: @user1350102: Right, the table name goes after IF NOT EXISTS. Thanks, amended.
• dw8547: @ErwinBrandstetter, could we use $$ ... $$ LANGUAGE plpgsql; instead of $func$ ... $func$ LANGUAGE plpgsql;? What is the difference between these two? Is there a situation where one is preferable to the other? I'm wondering because I've been using the former.
• Erwin Brandstetter: @dw8547: You could. No significance. It's just dollar-quoting. See: stackoverflow.com/questions/12144284/…. I use $func$ instead of just $$ for clarity and to avoid problems with nested dollar-quoting.

Answer 2

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.