Re: Function RETURNS SETOF ??? - Mailing list pgsql-general

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
inplace 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
'RETURNSSETOF' 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@Yahoo.com #



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