From: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | Benjie Buluran <benjie(dot)buluran(at)igentechnologies(dot)com> |
Cc: | pgadmin-support(at)postgresql(dot)org |
Subject: | Re: Inserting data from one database to another using stored functions |
Date: | 2011-01-06 11:02:08 |
Message-ID: | 4D25A130.5020601@lelarge.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Grazvydas Valeika | 2011-01-06 11:02:18 | Re: feature request |
Previous Message | Guillaume Lelarge | 2011-01-06 10:55:32 | Re: Database Port Problem in PostgreSQL Installation. |