Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

1 Answer

up vote 4 down vote accepted

Answer is yes. :)

CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
  RETURNS VOID 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$ LANGUAGE plpgsql;

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.

share|improve this answer
Thank you again, it works but you've got a little mistake. It should be 'CREATE TABLE IF NOT EXISTS %I', right? – user1350102 May 23 at 17:17
@user1350102: Right, the table name goes after IF NOT EXISTS. Thanks, amended. – Erwin Brandstetter May 23 at 21:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.