CODEWITHSUNDEEP
sqlpostgresql
rahman
rahman

Reputation: 4948

passing table name as argument to a function create a table

I have this function as:

CREATE OR REPLACE FUNCTION create_table(tableName varchar)
  RETURNS void AS
$BODY$
BEGIN
   EXECUTE format('CREATE TABLE IF NOT EXISTS %L(
        link_id integer NOT NULL);',tableName);

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION create_table(varchar)
  OWNER TO postgres;

suppose I execute the function like this:

select * from create_table('me')

I get this error:

ERROR:  syntax error at or near "'me'"
LINE 1: CREATE TABLE IF NOT EXISTS 'me'(
                                   ^
QUERY:  CREATE TABLE IF NOT EXISTS 'me'(
        link_id integer NOT NULL);
CONTEXT:  PL/pgSQL function create_table(character varying) line 4 at EXECUTE statement

********** Error **********

ERROR: syntax error at or near "'me'"
SQL state: 42601
Context: PL/pgSQL function create_table(character varying) line 4 at EXECUTE statement

Could you please let me know what this error mean and how to solve it? thanks

Upvotes: 0

Views: 46

Answers (1)

ruakh
ruakh

Reputation: 183251

From the documentation for format:

  • I treats the argument value as an SQL identifier, double-quoting it if necessary.
  • L quotes the argument value as an SQL literal.

You want the former, not the latter. So:

   EXECUTE format('CREATE TABLE IF NOT EXISTS %I(
        link_id integer NOT NULL);',tableName);

(Disclaimer: I don't have PostgreSQL instance handy to test with, so there may still be some other problems after you correct the %L to %I.)

Upvotes: 1

Related Questions