|
Hi Eric, I have tested your reply and it works J! Thank you for your help! By the way, here’s what I did with the calling stored function (“sf
DBa”): CREATE OR REPLACE FUNCTION
sp_update_serialnumber(pserialnumber character varying, pActivityId integer) RETURNS void AS $BODY$ DECLARE r record; BEGIN
UPDATE TABLESSERIALNUM SET SerialNumber = pSerialNumber WHERE ActivityID =
pActivityId ;
BEGIN
PERFORM * FROM dblink('dbname=testdb port=5432 user=postgres password=123456', 'SELECT
* FROM sp_insert_detailtable('|| pActivityId ||', '|| pserialnumber ||')') as
r(result character varying(50));
END; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; Regards, Benjie From: Eric McKeeth
[mailto:eldin00@xxxxxxxxx] On Thu, Jan 6, 2011 at 6:56 PM, Benjie Buluran <benjie.buluran@xxxxxxxxxxxxxxxxxxxx>
wrote: 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
No virus found in this message. |
