7

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?

15

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.

  • Thank you again, it works but you've got a little mistake. It should be 'CREATE TABLE IF NOT EXISTS %I', right? – Alan Cor May 23 '13 at 17:17
  • @user1350102: Right, the table name goes after IF NOT EXISTS. Thanks, amended. – Erwin Brandstetter May 23 '13 at 21:16
  • @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. – dw8547 Sep 20 '17 at 9:57
  • @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. – Erwin Brandstetter Sep 20 '17 at 12:47
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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