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. postgresql.org/docs/9.5/static/… — BillRobertson42
– BillRobertson42, Commented Jun 22, 2016 at 3:16

Community · Accepted Answer · 2017-05-23 12:02:08Z

2

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?

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Jun 22, 2016 at 3:20

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexander Kleinhans Over a year ago

You're awesome! Thanks!

Collectives™ on Stack Overflow

prepared statement inside function in postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related