CODEWITHSUNDEEP
cpostgresqlembedded-database
Luca Parisi
Luca Parisi

Reputation: 13

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

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"

Upvotes: 1

Views: 899

Answers (1)

Michał Niklas
Michał Niklas

Reputation: 54332

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)

Upvotes: 2

Related Questions