Re: Function RETURNS SETOF ???

Thalis A. Kalfigopoulos wrote:
> Helloppl,
>
> I have a long query that (summerized) looks something like:
>
> SELECT A.a,B.b
> FROM A,B
> WHERE A.x=B.x AND (A.y=const1 OR A.y=const2 OR A.y=const3);
>
> where the user provides const1,2,3 at runtime. The problem is in creating a function out of it:
> CREATE FUNCTION myfunc(int4,int4,int4) RETURNS <???????> AS 'SELECT A.a,B.b FROM A,B WHERE A.x=B.x AND (A.y= $1 OR A.y= $2 OR A.y= $3)' LANGUAGE 'sql';
>
> The question is what do I set as the return value? I'm returning a SETOF something but not something specific to put in place of <???????>. I tried 'RETURNS SETOF (int4,int4)' but didn't work.
>
> One workaround I figured was to create a new view that would give the schema definition I need to reference in my 'RETURNS SETOF' clause. This succeeds in creating the function, but the function doesn't work. I do a select myfunc(1,2,3) and I get something like:
>
> ?column?
> -----------
> 136361584
> 136361584
> 136361584
> (3 rows)
>
> At first I thought it was oids, but the aren't.
> Any ideas what this result is and how I can make this thing work?

The result is the memory address(es) of the heap tuples used
in the executor. Not that useful.

In the v7.2 development tree we currently have this:

CREATE TABLE A (x integer, y integer, a text);
CREATE TABLE B (x integer, b text);

INSERT INTO A VALUES (1, 11, 'one from A');
INSERT INTO A VALUES (2, 22, 'two from A');
INSERT INTO A VALUES (3, 33, 'three from A');

INSERT INTO B VALUES (1, 'one from B');
INSERT INTO B VALUES (2, 'two from B');
INSERT INTO B VALUES (3, 'three from B');

CREATE FUNCTION myfunc (refcursor, integer, integer, integer)
RETURNS refcursor AS '
DECLARE
curs ALIAS FOR $1;
y1 ALIAS FOR $2;
y2 ALIAS FOR $3;
y3 ALIAS FOR $4;
BEGIN
OPEN curs FOR SELECT A.a, B.b
FROM A, B
WHERE A.x = B.x AND
(A.y = y1 OR A.y = y2 OR A.y = y3);
RETURN curs;
END;'
LANGUAGE 'plpgsql';

BEGIN;
SELECT myfunc('c1', 11, 22, 44);
myfunc
--------
c1
(1 row)

FETCH ALL IN c1;
a | b
------------+------------
one from A | one from B
two from A | two from B
(2 rows)

CLOSE c1;
COMMIT;

BEGIN;
SELECT myfunc('c1', 33, 44, 55);
myfunc
--------
c1
(1 row)

FETCH ALL IN c1;
a | b
--------------+--------------
three from A | three from B
(1 row)

CLOSE c1;
COMMIT;

BEGIN;
SELECT myfunc('c1', 0, 0, 0);
myfunc
--------
c1
(1 row)

FETCH ALL IN c1;
a | b
---+---
(0 rows)

CLOSE c1;
COMMIT;

I don't know when v7.2 will happen, but I think it shouldn't
take as long as v7.1 did.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com