Dynamic table name for INSERT INTO query

Question

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.

If you have multiple tables with the same schema, such that they contain the same "sort" of data, that's usually an indication that they ought to be a single table with an additional column to contain the data that is probably, currently, embedded as part of the table *names*. I.e. if you were modelling an organization, you almost certainly shouldn't have `maleEmployees` and `femaleEmployees` tables. — Damien_The_Unbeliever, Nov 05 '14 at 07:31
Is it possible that `"schemaname"."Table_1"` has zero rows? What is the output of: `SELECT count("Col_A") FROM "schemaname"."Table_1";` — Antoan Milkov, Nov 05 '14 at 09:53

score 1 · Answer 1 · edited May 23 '17 at 10:26

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"?
- Are PostgreSQL column names case-sensitive?
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:

Table name as a PostgreSQL function parameter

Dynamic table name for INSERT INTO query

1 Answers1

Audited code

Function with parameter

Linked