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