Обсуждение: How to use dblink within pl/pgsql function:

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

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

Thanks to Joe Conway for pointing out a couple of typos in what I posted
(the original code that I used didn't have quite as many, I edited it to
try to simplify).  He also pointed out that I had not initialised my
'count' variable, which was a genuine mistake.  I am pleased to say that
the corrected function does work as expected:

CREATE OR REPLACE FUNCTION find() RETURNS INTEGER AS '
  DECLARE
    count INTEGER := 0;
    myrec RECORD;
  BEGIN
  FOR myrec IN SELECT * FROM DBLINK(''hostaddr=127.0.0.1'',
   ''select ra, decl from twomass where errbox &&
   box(point(120.45,0.5),point(120.50,0.75))'') as
     temp(x float8, y float8) LOOP
     count := count + 1;
  END LOOP;
  RETURN count;
END; ' LANGUAGE 'plpgsql';

I can use it in a query such as:

SELECT * FROM find();

And it returns the number of rows returned from the join using R-trees.
Of course there are easier ways of doing what this function does, I just
wanted to get that working as a basis on which to build something more
advanced.

--
Clive Page