Estoy creando un procedimiento almacenado en PostgreSQL que primero verificará en base a 'ID' si los datos están presentes en la tabla dada. En caso afirmativo, muévalo a otra tabla e inserte el registro más nuevo en el nombre de la tabla dada. Tengo un procedimiento almacenado escrito donde lo probé con valores codificados y funciona como lo necesito, pero cuando trato de hacerlo genérico, es decir, creando variables y luego pasando esas variables dentro de las consultas, arroja un error. Hice referencia a continuación a los SO links y al enlace de documentación oficial y pude modificar mi procedimiento almacenado:
A continuación se muestra mi procedimiento almacenado:
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; Entonces, mientras creating variables , solo usé la opción EXECUTE y al calling queries , usé la opción EXECUTE FORMAT(...) .
Y cuando trato de llamar a esto, obtengo el siguiente 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¿Qué me estoy perdiendo aquí?
Entonces, me gustaría responder mi propia pregunta, tal vez pueda ayudar a alguien como yo. Pude corregir el código anterior modificando la cadena actualtablename donde también estaba configurando el schema name . Por lo tanto, agregué una declaración SET dentro del procedimiento para configurar nombres de esquema donde deben realizarse las operaciones previstas y funcionó para mí.
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; De alguna manera, este procedimiento se almacena bajo el esquema public . Entonces, mientras lo llamo, tengo que usar los siguientes comandos:
set search_path to public; call compareDups(12,'{"name":"CTTT"}','sampletesting');