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

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(at)gmail(dot)com]
Sent: Friday, January 07, 2011 4:03 PM
To: Benjie Buluran
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Inserting data from one database to another using
stored functions

On Thu, Jan 6, 2011 at 6:56 PM, Benjie Buluran
<benjie(dot)buluran(at)igentechnologies(dot)com> 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

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

_____

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1191 / Virus Database: 1435/3364 - Release Date: 01/06/11