How to create a table whose name is taken from a string in PostgreSQL?

Question

I'm writing an embedded SQL application in C language. The application receives a string from a function and I need to create a table whose name is taken from that string. I have to create several tables with different names, but I don't know the number and the name of the tables from the start.

This is what i want to do:

tablename = function();
...
EXEC SQL CREATE TABLE :tablename ( ... );

But I got this error:

ERROR: syntax error at or near ":tablename"

First create a string containing a piece of SQL and then execute this piece of SQL. Beware of SQL injection, you might loose everything.... Check the quote_ident() function in PostgreSQL. — Frank Heikens
– Frank Heikens, Commented Apr 30, 2015 at 7:55

Michał Niklas · Accepted Answer · 2015-04-30 10:35:33Z

2

If you have to create the same data structures but with unique names then create procedure that creates such table:

CREATE OR REPLACE FUNCTION create_temp_table(table_name varchar)
  RETURNS void AS
$BODY$
BEGIN
    EXECUTE 'CREATE TABLE ' ||  quote_ident(table_name) || ' (id SERIAL, kn VARCHAR, kv VARCHAR)';
END;
$BODY$
LANGUAGE plpgsql;

You can call it from SQL via:

SELECT create_temp_table('tmp_table_31');

(notice use of quote_ident() to prevent SQL Injection)

answered Apr 30, 2015 at 10:35

Michał Niklas

54.5k19 gold badges77 silver badges125 bronze badges

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

Collectives™ on Stack Overflow

How to create a table whose name is taken from a string in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related