prepared statement inside function in postgres

Question

I can do the following in MySQL, but would like to know how to do the equivalent in PostgreSQL. I have to wrap the query in a string because the table name is variable (can't just run CREATE TABLE). This is all in a MySQL stored procedure / Postgres function.

    SET @createTable = CONCAT("CREATE TABLE ", table_name, "(
              id int(11) NOT NULL AUTO_INCREMENT,
              something varchar(255) NOT NULL");
    PREPARE createTableStmt FROM @createTable;
    EXECUTE createTableStmt;
    DEALLOCATE PREPARE createTableStmt;

Can someone please tell me how to do this in Postgres?

It's called dynamic sql in Postgres. https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN — BillRobertson42, Jun 22 '16 at 03:16

score 2 · Accepted Answer · edited May 23 '17 at 12:02

To run it once, just run the concatenated CREATE TABLE statement.

To encapsulate the functionality for repeated use with variable table name, use a plpgsql function with EXECUTE:

CREATE OR REPLACE FUNCTION create_my_table(_tbl text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('CREATE TABLE %I (id serial, something varchar(255) NOT NULL)', _tbl);
END
$func$ LANGUAGE plpgsql;

Call:

SELECT create_my_table('foo_table');

To drop:

DROP create_my_table(text);

There are many related answers here. Try a search.

Or if you only need it for the current session you can make it a temporary function that is dropped at the end of the session automatically:

CREATE OR REPLACE FUNCTION pg_temp.create_my_table(_tbl text) ...

Use a serial column to implement AUTOINCREMENT in Postgres:

Auto increment SQL function

You might want to make the column PRIMARY KEY as well.
And varchar(255) rarely makes sense. I would just use text instead:

Should I add an arbitrary length limit to VARCHAR columns?

You're awesome! Thanks! – Alexander Kleinhans Jun 22 '16 at 03:49 — Alexander Kleinhans, Jun 22 '16 at 03:49

prepared statement inside function in postgres

1 Answers1