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
– Adrian Klaver, Commented Jul 19, 2020 at 15:22

whatsinthename · Accepted Answer · 2020-07-19 14:34:29Z

2

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

answered Jul 19, 2020 at 14:34

whatsinthename

2,1771 gold badge30 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adrian Klaver Over a year ago

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

whatsinthename Over a year ago

So you mean I should specify the set search_path command before creating procedure so that it would be creating under that schema?

Adrian Klaver Over a year ago

Yes. Though the better option is to do CREATE OR REPLACE PROCEDURE testing.compareDups(...) Then there is no guessing.

whatsinthename Over a year ago

Yeah let me try this. Thanks @Adrian ;)

Collectives™ on Stack Overflow

Dynamically passing db and table names using stored procedure in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related