Postgresql - Dynamic Value as Table Name

PostgreSQL - dynamic value as table name

You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant.

DO
$$
BEGIN
EXECUTE format('CREATE TABLE %I AS SELECT * FROM backup', 'backup_' || to_char(CURRENT_DATE,'yyyy-mm-dd'));
END;
$$ LANGUAGE plpgsql;

The format(...) function's %I and %L format-specifiers do proper identifier and literal quoting, respectively.

For literals I recommend using EXECUTE ... USING rather than format(...) with %L, but for identifiers like table/column names the format %I pattern is a nice concise alternative to verbose quote_ident calls.

How to use variable as table name in plpgsql

Use dynamic SQL with EXECUTE, simplify, and escape identifiers properly:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS void AS
$func$
DECLARE
   v_table text;
BEGIN
   FOR v_table IN
      SELECT table_name  
      FROM   information_schema.tables 
      WHERE  table_catalog = 'my_database' 
      AND    table_schema = 'public'
      AND    table_name NOT LIKE 'z_%'
   LOOP
      EXECUTE format('DELETE FROM %I v WHERE v.id > (SELECT max(id) FROM %I)'
                    , v_table, 'z_' || v_table);
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Table names may need to be quoted to defend against syntax errors or even SQL injection! I use the convenient format() to concatenate the DELETE statement and escape identifiers properly.

• A separate SELECT would be more expensive. You can do it all with a single DELETE statement.

Related:

• Table name as a PostgreSQL function parameter

Aside:

You might use the (slightly faster) system catalog pg_tables instead:

      SELECT tablename
      FROM   pg_catalog.pg_tables
      WHERE  schemaname = 'public'
      AND    tablename NOT LIKE 'z_%'

See:

• How to check if a table exists in a given schema

table_catalog in information_schema.tables has no equivalent here. Only tables of the current database are visible anyway. So the above predicate WHERE table_catalog = 'my_database' produces an empty result set when connected to the wrong database.

Postgresql dynamic function with current table name

TG_TABLE_NAME is a special, trigger variable, which is only available inside trigger functions. Your create_audit_table() is not a trigger function.

Also, you constantly redefining your real trigger function (if_modified_func()), which "invalidates" any earlier created triggers.

Create your trigger function without the dynamic SQL magic (dynamic SQL will only need to insert values to these audit tables). Then, you can add your audit logic to a table with:

CREATE TRIGGER audit_trigger_row
  AFTER INSERT OR UPDATE OR DELETE
  ON public.<your_table_name>
  FOR EACH ROW
  EXECUTE PROCEDURE <your_audit_trigger_function_name>();

You can put this (but only this -- maybe with drop if exists) inside a function, to allow attaching this audit logic more easily.

Notes:

• Inside the trigger function, you cannot use a %ROWTYPE variable (because you don't know the exact table. you only have its name). The solution is simple: just use the RECORD type instead (you will need a cast upon inserting the values though).
• Don't use single quotes for such long strings. Use the $your_keyword$<string_value>$your_keyword$ format instead. With possibly the format() function instead of just concatenating values. Your code will be much more readable.

Edit: to utilize your RECORD variable, you should either:

• Initialize it with a structure. You can do this in your case with f.ex.

  SELECT nextval('audit.'|| quote_ident(TG_TABLE_NAME) || '_event_id_seq') AS event_id,
         TG_TABLE_SCHEMA                        AS schema_name,
         TG_TABLE_NAME                          AS table_name,
         TG_RELID                               AS relid,
         session_user                           AS session_user_name,
         current_timestamp                      AS action_tstamp_tx,
         statement_timestamp()                  AS action_tstamp_stm,
         clock_timestamp()                      AS action_tstamp_clk,
         txid_current()                         AS transaction_id,
         current_setting('application_name')    AS application_name,
         inet_client_addr()                     AS client_addr,
         inet_client_port()                     AS client_port,
         current_query()                        AS client_query,
         substring(TG_OP, 1, 1)                 AS action,
         NULL::hstore                           AS row_data,
         NULL::hstore                           AS changed_fields,
         FALSE                                  AS statement_only
  INTO   audit_row;
  

• Use the predefined names of the ROW() constructor. The first column's name if f1, the second's is f2, etc.

  audit_row.f15 = hstore(OLD.*) - excluded_cols;
  

After choosing one of the above methods, you should insert the row like:

EXECUTE format('INSERT INTO audit.%1$s VALUES (($1::text::audit.%1$s).*)', quote_ident(TG_TABLE_NAME)) USING audit_row;

Note: even the cast to text is required due to the fact that EXECUTE cannot know the actual structure of audit_row.

http://rextester.com/GUAJ1339

Dynamically passing db and table names using stored procedure in PostgreSQL

So, I would like to answer my own question maybe it can help someone like me. I was able to fix the above code by modifying the actualtablename string where I was setting up the schema name too. So, I added a SET statement inside the procedure for setting up schema names where the intended operations need to take place and it worked for me.

CREATE OR REPLACE PROCEDURE compareDups(ab integer, b json, tablename varchar)
AS $$
DECLARE 
  actualTableName varchar := tablename;
  histTableName varchar:= actualTableName ||'_hist';
  job_id Integer:=0;
BEGIN --<<<< HERE
  SET search_path to testing; -- Set the schema name
  EXECUTE 'SELECT id FROM '||actualTableName||' WHERE id =$1' INTO job_id USING ab;
  -- if there is data for id in the table then perform below operations
  IF job_id IS NOT NULL THEN
      EXECUTE FORMAT('INSERT INTO %I select * from %I where id = $1',histTableName,actualTableName) USING ab;
      EXECUTE FORMAT('DELETE FROM %I where id = $1',actualTableName) USING ab;
      EXECUTE FORMAT('INSERT INTO %I values($1,$2)',actualTableName) USING ab,b;
  -- if id is not present then create a new record in the actualTable
  ELSE    
      EXECUTE FORMAT('INSERT INTO %I values($1,$2)',actualTableName) USING ab,b;
  END IF;

END; --<<<< END HERE
$$
LANGUAGE plpgsql;  

Somehow, this procedure is getting stored under the public schema. So, while calling it I have to use the below commands:

set search_path to public;
call compareDups(12,'{"name":"CTTT"}','sampletesting');

---

Related Topics