Re: Stored Procedure

8.1, hmm, that's brand new.

But, still, it's quite some coding for a complete recordset, not ?


On 22 Nov 2005, at 19:59, Michael Fuhr wrote:


<excerpt>On Tue, Nov 22, 2005 at 07:29:37PM +0100, Yves Vindevogel
wrote:

<excerpt>Is there another way in PG to return a recordset from a
function than

to declare a type first ?

</excerpt>

In 8.1 some languages support OUT and INOUT parameters.


CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$

BEGIN

    y := y * 10;

    z := x * 10;

END;

$$ LANGUAGE plpgsql IMMUTABLE STRICT;


SELECT * FROM foo(1, 2);

 y  | z

----+----

 20 | 10

(1 row)


CREATE FUNCTION fooset(IN x integer, INOUT y integer, OUT z integer)

RETURNS SETOF record AS $$

BEGIN

    y := y * 10;

    z := x * 10;

    RETURN NEXT;

    y := y + 1;

    z := z + 1;

    RETURN NEXT;

END;

$$ LANGUAGE plpgsql IMMUTABLE STRICT;


SELECT * FROM fooset(1, 2);

 y  | z

----+----

 20 | 10

 21 | 11

(2 rows)


--

Michael Fuhr


---------------------------(end of
broadcast)---------------------------

TIP 4: Have you searched our list archives?


               http://archives.postgresql.org



</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>8.1, hmm, that's brand new.
But, still, it's quite some coding for a complete recordset, not ?

On 22 Nov 2005, at 19:59, Michael Fuhr wrote:

> On Tue, Nov 22, 2005 at 07:29:37PM +0100, Yves Vindevogel wrote:
>> Is there another way in PG to return a recordset from a function than
>> to declare a type first ?
>
> In 8.1 some languages support OUT and INOUT parameters.
>
> CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$
> BEGIN
>     y := y * 10;
>     z := x * 10;
> END;
> $$ LANGUAGE plpgsql IMMUTABLE STRICT;
>
> SELECT * FROM foo(1, 2);
>  y  | z
> ----+----
>  20 | 10
> (1 row)
>
> CREATE FUNCTION fooset(IN x integer, INOUT y integer, OUT z integer)
> RETURNS SETOF record AS $$
> BEGIN
>     y := y * 10;
>     z := x * 10;
>     RETURN NEXT;
>     y := y + 1;
>     z := z + 1;
>     RETURN NEXT;
> END;
> $$ LANGUAGE plpgsql IMMUTABLE STRICT;
>
> SELECT * FROM fooset(1, 2);
>  y  | z
> ----+----
>  20 | 10
>  21 | 11
> (2 rows)
>
> --
> Michael Fuhr
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

<smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.
Mahatma Ghandi.