Re: 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