Dynamically passing db and table names using stored procedure in PostgreSQL

user_7760910 0
Viewed 878

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:

  1. First SO link
  2. Second SO link

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?

postgresql stored-procedures
1 Answers

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