Thread: Inserting data from one database to another using stored functions

<div class="Section1"><p class="MsoNormal">Hi pgAdmin Support!<p class="MsoNormal"> <p class="MsoNormal">I’m stumped on
thisquestion for over 3 days now.<p class="MsoNormal"> <p class="MsoNormal">I need to run a stored function in Database
A(“sf DBa”) which calls a stored function in Database B (“sf DBb”).<p class="MsoNormal"> <p class="MsoNormal">Here’s
“sfDBa”:<p class="MsoNormal">CREATE OR REPLACE FUNCTION sp_update_serialnumber(pserialnumber character varying,
pActivityIdinteger)<p class="MsoNormal">  RETURNS void AS<p class="MsoNormal">$BODY$<p class="MsoNormal">BEGIN<p
class="MsoNormal">               UPDATE TABLESSERIALNUM SET SerialNumber = pSerialNumber WHERE ActivityID = pActivityId
;<pclass="MsoNormal"> <p class="MsoNormal">                BEGIN<p class="MsoNormal">                               
PERFORMdblink_connect('dbname=testdb port=5432 user=postgres password=123456');<p
class="MsoNormal">                               PERFORM dblink_exec('SELECT sp_insert_detailtable('|| pActivityId ||',
'||pserialnumber ||')');<p class="MsoNormal">                                PERFORM dblink_disconnect();<p
class="MsoNormal">               END;<p class="MsoNormal">END;<p class="MsoNormal">$BODY$<p class="MsoNormal"> 
LANGUAGEplpgsql VOLATILE<p class="MsoNormal">  COST 100;<p class="MsoNormal"> <p class="MsoNormal">Here’s “sf DBb”:<p
class="MsoNormal">CREATEOR REPLACE FUNCTION sp_insert_detailtable(pactivityid integer, pserialnumber character
varying)<pclass="MsoNormal">  RETURNS void AS<p class="MsoNormal">$BODY$<p class="MsoNormal">BEGIN<p
class="MsoNormal">               <p class="MsoNormal">                INSERT INTO DETAILTABLE(LogID, LogDetailSeq)<p
class="MsoNormal">               VALUES(pactivityid, pserialnumber);<p class="MsoNormal">END;<p
class="MsoNormal">$BODY$<pclass="MsoNormal">  LANGUAGE plpgsql VOLATILE<p class="MsoNormal">  COST 100;<p
class="MsoNormal"> <pclass="MsoNormal">I’m using the DEBUG function in pgAdmin, and I keep getting the “<b>statement
returningresults not allowed</b>” error in <i>PERFORM dblink_exec('SELECT sp_insert_detailtable('|| pActivityId ||',
'||pserialnumber ||')');</i> in this line.<p class="MsoNormal"> <p class="MsoNormal">Your help is highly appreciated!<p
class="MsoNormal"> <pclass="MsoNormal">Thanks and Best Regards,<p class="MsoNormal">Benjie</div> 

Hi,

Le 06/01/2011 07:01, Benjie Buluran a écrit :
> [...]
> 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.
> 

You should better do an EXECUTE rather than a PERFORM.

Anyway, this is not specific to pgAdmin. You should better ask on
pgsql-general.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com