I am renaming the Postgres tables inside the SQL executor(Postgres).
If the task is successful, I need a success mark or flag from the SQL executor(stored proc).
here there are two flags variables in my code ie "done2" and "i"
But the initiator only comes out, nothing comes from the output port.
I need one feature from Result port 1 for success or 0 for failure.
Can Anyone help me?
CREATE OR REPLACE FUNCTION func1()
RETURNS integer AS
$BODY$
DECLARE
cntold INTEGER := 0 ;
cntcur INTEGER := 0;
i INTEGER := 0;
BEGIN
SELECT INTO cntold count(*) FROM @Value(table_name_old) limit 10;
SELECT INTO cntcur count(*) FROM @Value(table_name) limit 10;
--SELECT INTO cntnew count(*) FROM @Value(table_name_new) limit 10;
IF cntold > 0 and cntcur > 0 THEN
EXECUTE 'DROP TABLE @Value(table_name_old)';
EXECUTE 'ALTER TABLE @Value(table_name) RENAME TO @Value(table_name_old)';
EXECUTE 'ALTER TABLE @Value(table_name_new) RENAME TO @Value(table_name)';
EXECUTE 'SELECT 1 AS DONE2';
i=1;
return i;
END IF;
END; $BODY$
LANGUAGE plpgsql VOLATILE