Dynamically passing db and table names using stored procedure in PostgreSQL

Question

I am creating a stored procedure in PostgreSQL which will first check on the basis of 'ID' if the data is present in the given table. If yes, then move it to some other table and insert the newest record in the given table name. I have a written a stored procedure where I tried it with hard-coded values and it works as I need but when I am trying to make it generic i.e creating variables and then passing those variables inside the queries then it throws an error. I referred below SO links and official documentation link and was able to modify my stored procedure:

Below is my Stored Procedure:

CREATE OR REPLACE PROCEDURE compareDups(ab integer, b json, tablename varchar)
AS $$
DECLARE 
  actualTableName varchar := 'testing.'||tablename;
  histTableName varchar:= actualTableName ||'_hist';
  job_id Integer:=0;
BEGIN --<<<< HERE
  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 as select * from %L 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;

So, while creating variables I have used just EXECUTE option and while calling queries I have used EXECUTE FORMAT(...) option.

And when I try to call this, I am getting below error:

ERROR:  syntax error at or near "select"
LINE 1: INSERT INTO "testing.sampletesting_hist" as select * from 't...
                                                    ^
QUERY:  INSERT INTO "testing.sampletesting_hist" as select * from 'testing.sampletesting' where id = $1
CONTEXT:  PL/pgSQL function comparedups(integer,json,character varying) line 10 at EXECUTE
SQL state: 42601

What am I missing over here?

What you are missing is that "testing.sampletesting_hist" should be "testing"."sampletesting_hist" Also this 'testing.sampletesting' is not valid. Identifiers need to be double quoted. — Adrian Klaver, Jul 19 '20 at 15:22

score 2 · Accepted Answer · answered Jul 19 '20 at 14:34

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');

Well you did not schema qualify the ```PROCEDURE``` name so it got created in the first schema in the ```search_path```. Setting the search_path in the procedure only works inside the procedure. Explicit is better then implicit if you want an object to be assigned to a schema then name it that way e.g. my_schema.compareDups. Or do the ```SET search_path``` immediately before the ```CREATE``` — Adrian Klaver, Jul 19 '20 at 15:27
So you mean I should specify the set search_path command before creating procedure so that it would be creating under that schema? — whatsinthename, Jul 19 '20 at 17:41
Yes. Though the better option is to do ```CREATE OR REPLACE PROCEDURE testing.compareDups(...)``` Then there is no guessing. — Adrian Klaver, Jul 19 '20 at 17:45

Dynamically passing db and table names using stored procedure in PostgreSQL

1 Answers1