PostgreSQL Accessing multiple database from stored function

Question

I want to access different databases from postgresql stored functions.

For example:

CREATE OR REPLACE FUNCTION test ()  RETURNS SETOF volume AS
$BODY$

SELECT * FROM db2.volume
ORDER BY db2.volume.volumeID

$BODY$  
LANGUAGE sql STABLE  
COST 100  
ROWS 1000;

any input will be great help.

Regards, Manasi

damir · Accepted Answer · 2010-12-08 10:17:39Z

2

what you need is database link

answered Dec 8, 2010 at 10:17

damir

2,0083 gold badges18 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MySQL DBA Over a year ago

Well but in db link is it possible I can change database name at runtime.

damir Over a year ago

why not ? SELECT dblink_connect('conn', 'dbname=yourdb');

MySQL DBA Over a year ago

CREATE OR REPLACE FUNCTION test (dbname character varying) RETURNS SETOF volume AS $BODY$ SELECT * FROM dblink_connect('dbname=$1') ORDER BY db2.volume.volumeID $BODY$ LANGUAGE sql STABLE COST 100 ROWS 1000;

Collectives™ on Stack Overflow

PostgreSQL Accessing multiple database from stored function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related