pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction that returns a tuple.
3+ messages / 3 participants
[nested] [flat]
* Function that returns a tuple.
@ 2000-10-18 13:32 Claudio Jose Zanardi Grillo <cjg@certi.ufsc.br>
2000-10-18 15:36 ` Re: Function that returns a tuple. Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Claudio Jose Zanardi Grillo @ 2000-10-18 13:32 UTC (permalink / raw)
To: pgsql-sql
How can I write a function in sql or pl- pgsql that returns a set of
tuples ?
Thanks.
Claudio
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function that returns a tuple.
2000-10-18 13:32 Function that returns a tuple. Claudio Jose Zanardi Grillo <cjg@certi.ufsc.br>
@ 2000-10-18 15:36 ` Josh Berkus <josh@agliodbs.com>
2000-10-18 16:32 ` Re: Function that returns a tuple. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2000-10-18 15:36 UTC (permalink / raw)
To: pgsql-sql; Jeff MacDonald <jeff@pgsql.com>
Claudio,
> How can I write a function in sql or pl- pgsql that returns a set of
> tuples ?
I've had the same question myself for the past month, which has gone
unanswered (hint, hint, Jeff!). Currently I'm using custom functions to
build views and then referenceing the views:
CREATE FUNCTION fun_generate_statistics (
do a bunch of stuff, ending with:
CREATE VIEW vw_generate_statistics (
bunch of stuff);
);
Then the front-end code references vw_generate_statistics.
Not elegant, but sufficient until I hear from Jeff or Jan Wieck.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Function that returns a tuple.
2000-10-18 13:32 Function that returns a tuple. Claudio Jose Zanardi Grillo <cjg@certi.ufsc.br>
2000-10-18 15:36 ` Re: Function that returns a tuple. Josh Berkus <josh@agliodbs.com>
@ 2000-10-18 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2000-10-18 16:32 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
>> How can I write a function in sql or pl- pgsql that returns a set of
>> tuples ?
> I've had the same question myself for the past month,
This is easy enough in terms of what you do in the function, but it's
not as useful as you might think because of the weird POSTQUEL-derived
syntax for calling such a function.
The key concept is that a table name is also a type name for the struct
type of its rows. So for example,
create table tab1 (f1 int, f2 text);
create function find_tab(int) returns setof tab1 as
'select * from tab1 where f1 = $1'
language 'sql';
insert into tab1 values (1,'one');
insert into tab1 values (1,'another one');
insert into tab1 values (2, 'two');
The problem is that struct types aren't really supported by the
expression evaluator; the only thing you can usefully do with them
is select out one column. So this doesn't work very well:
select find_tab(1);
?column?
------------
1074362792
1074362792
(2 rows)
The obvious way to do the selecting of a column is
select find_tab(1).f2;
ERROR: parser: parse error at or near "."
but for some reason the parser won't accept that. (Perhaps this could
be fixed, I haven't looked into it.) The only way to invoke such a
function at present is to apply it to a column from some other table and
write it as a POSTQUEL dot-function:
select tab2.fld1.find_tab.f2 from tab2;
which is the POSTQUEL notation for what a saner person would call
select find_tab(tab2.fld1).f2 from tab2;
Bizarre syntax aside, the real problem with this whole approach is that
if you've got a function returning tuple(s), you probably want to be
able to get at the tuple elements and do things with them, *without*
re-evaluating the function for each such use. So I think what we really
want to do is to allow functions returning tuple sets to be elements
of a FROM clause:
select f1 + 1, f2 from find_tab(33);
This has been speculated about but no one's looked at what it would take
to make it work.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-10-18 16:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-18 13:32 Function that returns a tuple. Claudio Jose Zanardi Grillo <cjg@certi.ufsc.br>
2000-10-18 15:36 ` Josh Berkus <josh@agliodbs.com>
2000-10-18 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
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