By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

How to use dblink within pl/pgsql function:

P: n/a
What I'd like to do is use dblink to extract a few rows from a remote
database and manipulate these within a function in pl/pgsql. Something
like this:

CREATE OR REPLACE FUNCTION find() RETURNS INTEGER AS '
DECLARE
count INTEGER:
myrec RECORD;
BEGIN
FOR myrec IN SELECT * FROM DBLINK(''select x,y from mytab'') as
temp(x integer, y real) LOOP
count := count + 1;
END LOOP;
RETURN count;
END; ' LANGUAGE 'plpgsql';
But this syntax does not work, and I cannot find a form which does work.
Does anyone know how to do this?

Thanks in advance.

--
Clive Page
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Clive Page wrote:
What I'd like to do is use dblink to extract a few rows from a remote
database and manipulate these within a function in pl/pgsql. Something
like this:

CREATE OR REPLACE FUNCTION find() RETURNS INTEGER AS '
DECLARE
count INTEGER:
myrec RECORD;
BEGIN
FOR myrec IN SELECT * FROM DBLINK(''select x,y from mytab'') as
temp(x integer, y real) LOOP
count := count + 1;
END LOOP;
RETURN count;
END; ' LANGUAGE 'plpgsql';
But this syntax does not work, and I cannot find a form which does work.
Does anyone know how to do this?


You didn't show us the specific error you get, making it difficult to help.

Offhand I see two errors in your script above unrelated to the use of
dblink, and possibly one related to dblink. First, the line "count
INTEGER:" ends in a colon instead of the required semicolon. Second, if
you don't initialize "count" to something other than NULL, adding 1 to
it will still add null. Try this:

CREATE OR REPLACE FUNCTION find() RETURNS INTEGER AS '
DECLARE
count INTEGER := 0;
myrec RECORD;
BEGIN
FOR myrec IN SELECT * FROM DBLINK(''select x,y from mytab'') as
temp(x integer, y real) LOOP
count := count + 1;
END LOOP;
RETURN count;
END; ' LANGUAGE 'plpgsql';

The dblink issue is that you've used a form of dblink that requires you
to have previously set up a connection. If you call your find() function
without first establishing that connection, you'll get a "ERROR:
connection not available" or something similar. See the dblink docs.

Joe
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.