CODEWITHSUNDEEP
postgresqlfunctioninsertplpgsqldynamic-queries
Nishchal
Nishchal

Reputation: 1

Dynamic table name for INSERT INTO query

I am trying to figure out how to write an INSERT INTO query with table name and column name of the source as parameter.

For starters I was just trying to parametrize the source table name. I have written the following query. For now I am declaring and assigning the value of the variable tablename directly, but in actual example it would come from some other source/list. The target table has only one column.

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$
DECLARE
    tablename text;
BEGIN
   tablename := 'Table_1';
   EXECUTE 'INSERT INTO "Schemaname"."targettable"
   SELECT "Col_A"
   FROM "schemaname".'
   ||quote_ident(tablename);
END
$$ LANGUAGE PLPGSQL;

Although the query runs without any error no changes are reflected at the target table. On running the query I get the following output.

Query OK, 0 rows affected (execution time: 296 ms; total time: 296 ms)

I want the changes to be reflected at the target table. I don't know how to resolve the problem.

Upvotes: 0

Views: 4099

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

Audited code

CREATE OR REPLACE FUNCTION foo()
   RETURNS void AS
$func$
DECLARE
   _tbl text := 'Table_1';  -- or 'table_1'?
BEGIN       
   EXECUTE 'INSERT INTO schemaname.targettable(column_name)
   SELECT  "Col_A"
   FROM    schemaname.' || quote_ident(_tbl);  -- or "Schemaname"?
END
$func$  LANGUAGE plpgsql;

  • Always use an explicit target list for persisted INSERT statements.

  • You can assign variables at declare time.

  • It's a wide-spread folly to use double-quoted identifiers to preserve otherwise illegal spelling. You have to keep double-quoting the name for the rest of its existence. One or more of those errors seem to have crept into your code: "Schemaname" or "schemaname"? Table_1 or "Table_1"?

  • When you provide an identifier like a table name as text parameter and escape it with quote_ident(), it is case sensitive!
    Identifiers in SQL code are cast to lower case unless double-quoted. But quote-ident() (which you must use to defend against SQL injection) preserves the spelling you provide with double-quotes where necessary.

Function with parameter

CREATE OR REPLACE FUNCTION foo(_tbl text)
   RETURNS void AS
$func$
BEGIN       
   EXECUTE 'INSERT INTO schemaname.targettable(column_name)
   SELECT  "Col_A"
   FROM    schemaname.' || quote_ident(_tbl);
END
$func$  LANGUAGE plpgsql;

Call:

SELECT foo('tablename');  -- tablename is case sensitive

There are other ways:

Upvotes: 1

Related Questions