Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stored Procedure

Stored Procedure

From
Yves Vindevogel
Date:
Is there another way in PG to return a recordset from a function than
to declare a type first ?


create function fnTest () returns setof
myDefinedTypeIDontWantToDefineFirst ...



Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>Is there another way in PG to return a recordset from a function than
to declare a type first ?

create function fnTest () returns setof
myDefinedTypeIDontWantToDefineFirst ...


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.
Attachment

Re: Stored Procedure

From
"Jim Buttafuoco"
Date:
create function abc() returns setof RECORD ...

then to call it you would do
select * from abc() as (a text,b int,...);




---------- Original Message -----------
From: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Sent: Tue, 22 Nov 2005 19:29:37 +0100
Subject: [PERFORM] Stored Procedure

> Is there another way in PG to return a recordset from a function than
> to declare a type first ?
>
> create function fnTest () returns setof
> myDefinedTypeIDontWantToDefineFirst ...
>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
------- End of Original Message -------

Re: Stored Procedure

From
Michael Fuhr
Date:
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

Re: Stored Procedure

From
Yves Vindevogel
Date:
But this does not work without the  second line, right ?

BTW, the thing returned is not a record.  It's a bunch of fields, not
a complete record or fields of multiple records.

I'm not so sure it works.


On 22 Nov 2005, at 19:42, Jim Buttafuoco wrote:


<excerpt>create function abc() returns setof RECORD ...


then to call it you would do

select * from abc() as (a text,b int,...);





---------- Original Message -----------

From: Yves Vindevogel <<yves.vindevogel@implements.be>

To: pgsql-performance@postgresql.org

Sent: Tue, 22 Nov 2005 19:29:37 +0100

Subject: [PERFORM] Stored Procedure


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

to declare a type first ?


create function fnTest () returns setof

myDefinedTypeIDontWantToDefineFirst ...


Met vriendelijke groeten,

Bien à vous,

Kind regards,


Yves Vindevogel

Implements

</excerpt>------- End of Original Message -------




</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>But this does not work without the  second line, right ?
BTW, the thing returned is not a record.  It's a bunch of fields, not a
complete record or fields of multiple records.
I'm not so sure it works.

On 22 Nov 2005, at 19:42, Jim Buttafuoco wrote:

> create function abc() returns setof RECORD ...
>
> then to call it you would do
> select * from abc() as (a text,b int,...);
>
>
>
>
> ---------- Original Message -----------
> From: Yves Vindevogel <yves.vindevogel@implements.be>
> To: pgsql-performance@postgresql.org
> Sent: Tue, 22 Nov 2005 19:29:37 +0100
> Subject: [PERFORM] Stored Procedure
>
>> Is there another way in PG to return a recordset from a function than
>> to declare a type first ?
>>
>> create function fnTest () returns setof
>> myDefinedTypeIDontWantToDefineFirst ...
>>
>> Met vriendelijke groeten,
>> Bien à vous,
>> Kind regards,
>>
>> Yves Vindevogel
>> Implements
> ------- End of Original Message -------
>
>
>
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.
Attachment

Re: Stored Procedure

From
Yves Vindevogel
Date:
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.
Attachment

Re: Stored Procedure

From
Michael Fuhr
Date:
On Tue, Nov 22, 2005 at 11:17:41PM +0100, Yves Vindevogel wrote:
> But this does not work without the  second line, right ?

What second line?  Instead of returning a specific composite type
a function can return RECORD or SETOF RECORD; in these cases the
query must provide a column definition list.

> BTW, the thing returned is not a record.  It's a bunch of fields, not a
> complete record or fields of multiple records.

What distinction are you making between a record and a bunch of
fields?  What exactly would you like the function to return?

> I'm not so sure it works.

Did you try it?  If you did and it didn't work then please post
exactly what you tried and explain what happened and how that
differed from what you'd like.

--
Michael Fuhr

Re: Stored Procedure

From
Michael Fuhr
Date:
On Tue, Nov 22, 2005 at 11:20:09PM +0100, Yves Vindevogel wrote:
> 8.1, hmm, that's brand new.

Yes, but give it a try, at least in a test environment.  The more
people use it, the more we'll find out if it has any problems.

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

How so?  The examples I posted are almost identical to how you'd
return a composite type created with CREATE TYPE or SETOF that type,
except that you declare the return columns as INOUT or OUT parameters
and you no longer have to create a separate type.  If you're referring
to how I wrote two sets of assignments and RETURN NEXT statements,
you don't have to do it that way: you can use a loop, just as you
would with any other set-returning function.

--
Michael Fuhr