pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStored Procedure
7+ messages / 3 participants
[nested] [flat]
* Stored Procedure
@ 2005-11-22 18:29 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 2 replies; 7+ messages in thread
From: Yves Vindevogel @ 2005-11-22 18:29 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Stored Procedure
@ 2005-11-22 18:42 Jim Buttafuoco <jim@contactbda.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 1 reply; 7+ messages in thread
From: Jim Buttafuoco @ 2005-11-22 18:42 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
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 -------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored Procedure
@ 2005-11-22 18:59 Michael Fuhr <mike@fuhr.org>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-11-22 18:59 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored Procedure
@ 2005-11-22 22:17 Yves Vindevogel <yves.vindevogel@implements.be>
parent: Jim Buttafuoco <jim@contactbda.com>
0 siblings, 1 reply; 7+ messages in thread
From: Yves Vindevogel @ 2005-11-22 22:17 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Stored Procedure
@ 2005-11-22 22:20 Yves Vindevogel <yves.vindevogel@implements.be>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Yves Vindevogel @ 2005-11-22 22:20 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Stored Procedure
@ 2005-11-23 05:05 Michael Fuhr <mike@fuhr.org>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-11-23 05:05 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored Procedure
@ 2005-11-23 05:13 Michael Fuhr <mike@fuhr.org>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2005-11-23 05:13 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-11-23 05:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-22 18:29 Stored Procedure Yves Vindevogel <yves.vindevogel@implements.be>
2005-11-22 18:42 ` Jim Buttafuoco <jim@contactbda.com>
2005-11-22 22:17 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-11-23 05:05 ` Michael Fuhr <mike@fuhr.org>
2005-11-22 18:59 ` Michael Fuhr <mike@fuhr.org>
2005-11-22 22:20 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-11-23 05:13 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox