[GENERAL] Inserting data from one database to another using stored functions

[Benjie Buluran]

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").

 

Here's "sf DBa":

CREATE OR REPLACE FUNCTION sp_update_serialnumber(pserialnumber character
varying, pActivityId integer)

  RETURNS void AS

$BODY$

BEGIN

                UPDATE TABLESSERIALNUM SET SerialNumber = pSerialNumber
WHERE ActivityID = pActivityId ;

 

                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