Thread: SQL: table function support
Hello
this patch add support of table functions syntax like ANSI SQL 2003.
CREATE OR REPLACE FUNCTION foo_sql(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
SELECT i, i+1, i+2
FROM generate_series(1, $1) g(i);
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION foo_plpgsql1(m integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
DECLARE r record;
BEGIN
FOR i IN 1..m LOOP
r = ROW(i, i+1, i+2);
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION foo_plpgsql2(m integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
DECLARE r record;
BEGIN
RETURN QUERY
SELECT i, i+1, i+2
FROM generate_series(1, m) g(i);
RETURN;
END;
$$ LANGUAGE plpgsql;
There are one significant difference to SRF with OUT variables.
Attributies declared in TABLE clause doesn't create local variables.
It's in conformance with SQL/PSM.
Regards
Pavel Stehule
Attachment
On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote: > this patch add support of table functions syntax like ANSI SQL 2003. I'm not necessarily opposed to this, but I wonder if we really need *more* syntax variants for declaring set-returning functions. The existing patchwork of features is confusing enough as it is... -Neil
Neil Conway <neilc@samurai.com> writes:
> On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote:
>> this patch add support of table functions syntax like ANSI SQL 2003.
> I'm not necessarily opposed to this, but I wonder if we really need
> *more* syntax variants for declaring set-returning functions.
I've been saying right along that we don't. The proposed patch adds
no measurable new functionality; its only reason to live is standards
compliance, and I'm not convinced that's worth the confusion. Our
implementation of functions is (and always will be) far enough away
from the standard that notational issues like this are hardly the top
of the problem list for someone wishing to import a spec-compliant
function.
(It's also worth asking where the import is coming from. Who implements
the spec syntax anyway? DB2 maybe, but when was the last time we heard
from anyone trying to migrate from DB2 to PG?)
regards, tom lane
Tom Lane escribió: > (It's also worth asking where the import is coming from. Who implements > the spec syntax anyway? DB2 maybe, but when was the last time we heard > from anyone trying to migrate from DB2 to PG?) Sourceforge? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane escribi�:
>> (It's also worth asking where the import is coming from. Who implements
>> the spec syntax anyway? DB2 maybe, but when was the last time we heard
>> from anyone trying to migrate from DB2 to PG?)
> Sourceforge?
They gave up on us years ago :-(
regards, tom lane
2008/6/10 Tom Lane <tgl@sss.pgh.pa.us>: > Neil Conway <neilc@samurai.com> writes: >> On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote: >>> this patch add support of table functions syntax like ANSI SQL 2003. > >> I'm not necessarily opposed to this, but I wonder if we really need >> *more* syntax variants for declaring set-returning functions. > > I've been saying right along that we don't. The proposed patch adds > no measurable new functionality; its only reason to live is standards > compliance, and I'm not convinced that's worth the confusion. Our > implementation of functions is (and always will be) far enough away > from the standard that notational issues like this are hardly the top > of the problem list for someone wishing to import a spec-compliant > function. a) current syntax is strange for beginers (and I am sure - isn't nice) - look to mailing lists. I belive so ansi syntax is better. b) it's needed for well SQL/PSM support. With table functions and RETURN QUERY we are neer standard. > > (It's also worth asking where the import is coming from. Who implements > the spec syntax anyway? DB2 maybe, but when was the last time we heard > from anyone trying to migrate from DB2 to PG?) > lot of smaller new databases respect ANSI SQL 200x well - not only db2 > regards, tom lane >
2008/6/10 Neil Conway <neilc@samurai.com>: > On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote: >> this patch add support of table functions syntax like ANSI SQL 2003. > > I'm not necessarily opposed to this, but I wonder if we really need > *more* syntax variants for declaring set-returning functions. The > existing patchwork of features is confusing enough as it is... > internally is table functions implemenation identical with SRF. Semantically is far - user's doesn't specify return type (what is from PostgreSQL), but specifies return table, what is more natural. What more - for users is transparent chaotic joice betwen "SETOF RECORD" for multicolumns sets and "SETOF type". Pavel > -Neil > > >
On Tue, 2008-06-10 at 06:42 +0200, Pavel Stehule wrote: > internally is table functions implemenation identical with SRF. It's not the internals that I'm concerned about. > Semantically is far - user's doesn't specify return type (what is from > PostgreSQL), but specifies return table, what is more natural. What > more - for users is transparent chaotic joice betwen "SETOF RECORD" > for multicolumns sets and "SETOF type". Well, I'd just like to see some thought about how this *entire* feature ought to work, rather than just adding new knobs and syntax variants incrementally and seemingly at random. Just because it happens to be in the standard isn't really a compelling reason to make an overly-complex part of the system even more complicated, IMHO... -Neil
2008/6/10 Neil Conway <neilc@samurai.com>: > On Tue, 2008-06-10 at 06:42 +0200, Pavel Stehule wrote: >> internally is table functions implemenation identical with SRF. > > It's not the internals that I'm concerned about. > >> Semantically is far - user's doesn't specify return type (what is from >> PostgreSQL), but specifies return table, what is more natural. What >> more - for users is transparent chaotic joice betwen "SETOF RECORD" >> for multicolumns sets and "SETOF type". > > Well, I'd just like to see some thought about how this *entire* feature > ought to work, rather than just adding new knobs and syntax variants > incrementally and seemingly at random. Just because it happens to be in > the standard isn't really a compelling reason to make an overly-complex > part of the system even more complicated, IMHO... > > -Neil > This feature has only little sense with plpgsql, but together with sql's functions allows more readable code. And is significant for SQL/PSM. what is more logical and consistent? create or replace function fx(a integer, out b integer, out c integer) returns setof record as $$ select a, b from foo where a = $1; $$ language sql; or create or replace function fx(a integer) returns table(b integer, c integer) as $$ select a, b from foo where a = $1; $$ language sql; Pavel > >
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> what is more logical and consistent?
They're both utterly arbitrary, but the "setof" syntax has been in
Postgres since forever, and applies to more things than just "record".
The other one doesn't fit in with anything else --- it's just a
syntactic wart.
regards, tom lane
Tom Lane escribió: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Tom Lane escribi�: > >> (It's also worth asking where the import is coming from. Who implements > >> the spec syntax anyway? DB2 maybe, but when was the last time we heard > >> from anyone trying to migrate from DB2 to PG?) > > > Sourceforge? > > They gave up on us years ago :-( Actually they migrated from 7.2 to DB2 because IBM paid for them to do it (and also because they were tripping on some deficiency at the time); but after that contract expired, they migrated back from DB2 to a newer Postgres. Obviously there wasn't much money invested in making big press splashes about it, though. I can't tell if they were using table function support though :-) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Mon, Jun 09, 2008 at 05:56:59PM -0700, Neil Conway wrote: > On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote: > > this patch add support of table functions syntax like ANSI SQL > > 2003. > > I'm not necessarily opposed to this, but I wonder if we really need > *more* syntax variants for declaring set-returning functions. The > existing patchwork of features is confusing enough as it is... The way we declare set-returning functions ranges from odd to byzantine. A clear, easy-to-understand syntax (even if it's just sugar over something else) like Pavel's would go a long way toward getting developers actually to use them. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Mon, Jun 09, 2008 at 05:56:59PM -0700, Neil Conway wrote:
>> I'm not necessarily opposed to this, but I wonder if we really need
>> *more* syntax variants for declaring set-returning functions. The
>> existing patchwork of features is confusing enough as it is...
> The way we declare set-returning functions ranges from odd to
> byzantine. A clear, easy-to-understand syntax (even if it's just
> sugar over something else) like Pavel's would go a long way toward
> getting developers actually to use them.
Apparently, whether the syntax is byzantine or not is in the eye of
the beholder. I find the TABLE() syntax to be *less* clear.
regards, tom lane
On Thu, Jun 12, 2008 at 12:33:57PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Mon, Jun 09, 2008 at 05:56:59PM -0700, Neil Conway wrote: > >> I'm not necessarily opposed to this, but I wonder if we really > >> need *more* syntax variants for declaring set-returning > >> functions. The existing patchwork of features is confusing enough > >> as it is... > > > The way we declare set-returning functions ranges from odd to > > byzantine. A clear, easy-to-understand syntax (even if it's just > > sugar over something else) like Pavel's would go a long way toward > > getting developers actually to use them. > > Apparently, whether the syntax is byzantine or not is in the eye of > the beholder. I find the TABLE() syntax to be *less* clear. I went and got reports from the field. Over the years, I've had to explain at great length and with no certain success to developers at a dozen different companies how to use OUT parameters. RETURNS TABLE(...) is *much* more intuitive to those people, who have a tendency to do things like create temp tables rather than figure out the OUT parameter syntax afresh. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Thu, 2008-06-12 at 12:05 -0700, David Fetter wrote: > On Thu, Jun 12, 2008 at 12:33:57PM -0400, Tom Lane wrote: > > David Fetter <david@fetter.org> writes: > > > On Mon, Jun 09, 2008 at 05:56:59PM -0700, Neil Conway wrote: > I went and got reports from the field. Over the years, I've had to > explain at great length and with no certain success to developers at a > dozen different companies how to use OUT parameters. RETURNS > TABLE(...) is *much* more intuitive to those people, who have a > tendency to do things like create temp tables rather than figure out > the OUT parameter syntax afresh. Regardless of whether anyone thinks they are byzantine (or not) if RETURNS TABLE() is in the standard. We should try and implement it if we can. Sincerely, Joshua D. Drake
On Thu, Jun 12, 2008 at 12:33:57PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Mon, Jun 09, 2008 at 05:56:59PM -0700, Neil Conway wrote: > >> I'm not necessarily opposed to this, but I wonder if we really need > >> *more* syntax variants for declaring set-returning functions. The > >> existing patchwork of features is confusing enough as it is... > > > The way we declare set-returning functions ranges from odd to > > byzantine. A clear, easy-to-understand syntax (even if it's just > > sugar over something else) like Pavel's would go a long way toward > > getting developers actually to use them. > > Apparently, whether the syntax is byzantine or not is in the eye of > the beholder. I find the TABLE() syntax to be *less* clear. Perhaps, but I can see explaining it to my over-busy-non-doc-reading developers much more easily than the existing choices. Of course then they will all want to write set returning functions, so I may end up regretting it. -dg -- David Gould daveg@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects.