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
