Hi pgSQL peeps!
I’m stumped on this question for over 3 days now.
I need to run a stored function in Database A (“sf DBa”) which calls a stored function in Database B (“sf DBb”).
BEGIN
PERFORM dblink_connect('dbname=testdb port=5432 user=postgres password=123456');
PERFORM dblink_exec('SELECT sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')');
PERFORM dblink_disconnect();
END;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Here’s “sf DBb”:
CREATE OR REPLACE FUNCTION sp_insert_detailtable(pactivityid integer, pserialnumber character varying)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO DETAILTABLE(LogID, LogDetailSeq)
VALUES(pactivityid, pserialnumber);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
I’m using the DEBUG function in pgAdmin, and I keep getting the “statement returning results not allowed” error in PERFORM dblink_exec('SELECT sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')'); in this line.
Your help is highly appreciated!
Thanks and Best Regards,
Benjie
dblink_exec is only for commands which return no result. Try replacing that line with the following and see if it helps:
PERFORM dblink('SELECT sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')');
-Eric
