On Thu, Dec 05, 2013 at 10:34:08PM -0500, Stephen Frost wrote:
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Noah Misch n...@leadboat.com writes:
Two naming proposals, ROWS FROM and TABLE FROM, got an ACK from more
than
one person apiece. I move that we settle on ROWS FROM.
I'm not
On Tue, Dec 03, 2013 at 02:27:06PM -0500, Tom Lane wrote:
Noah Misch n...@leadboat.com writes:
On Tue, Dec 03, 2013 at 10:03:32AM -0500, Stephen Frost wrote:
Alright, for my 2c, I like having this syntax include 'TABLE' simply
because it's what folks coming from Oracle might be looking for.
Noah Misch n...@leadboat.com writes:
Two naming proposals, ROWS FROM and TABLE FROM, got an ACK from more than
one person apiece. I move that we settle on ROWS FROM.
I'm not sufficiently annoyed by ROWS FROM to object. Other opinions?
regards, tom lane
--
Sent via
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Noah Misch n...@leadboat.com writes:
Two naming proposals, ROWS FROM and TABLE FROM, got an ACK from more
than
one person apiece. I move that we settle on ROWS FROM.
I'm not sufficiently annoyed by ROWS FROM to object. Other opinions?
Works well
On Mon, Dec 2, 2013 at 11:26 PM, Noah Misch n...@leadboat.com wrote:
On Mon, Dec 02, 2013 at 08:56:03PM -0500, Tom Lane wrote:
Noah Misch n...@leadboat.com writes:
... I propose merely changing the syntax to TABLE FOR ROWS (...).
Ugh :-(. Verbose and not exactly intuitive, I think. I don't
Noah Misch n...@leadboat.com writes:
On Mon, Dec 02, 2013 at 08:56:03PM -0500, Tom Lane wrote:
Ugh :-(. Verbose and not exactly intuitive, I think. I don't like
any of the other options you listed much better. Still, the idea of
using more than one word might get us out of the bind that a
* Tom Lane (t...@sss.pgh.pa.us) wrote:
After sleeping on it, your other suggestion of TABLE OF, or possibly
TABLE FROM, is starting to grow on me.
Who else has an opinion?
Alright, for my 2c, I like having this syntax include 'TABLE' simply
because it's what folks coming from Oracle might be
2013/12/3 Stephen Frost sfr...@snowman.net
* Tom Lane (t...@sss.pgh.pa.us) wrote:
After sleeping on it, your other suggestion of TABLE OF, or possibly
TABLE FROM, is starting to grow on me.
Who else has an opinion?
Alright, for my 2c, I like having this syntax include 'TABLE' simply
On Tue, Dec 03, 2013 at 10:03:32AM -0500, Stephen Frost wrote:
* Tom Lane (t...@sss.pgh.pa.us) wrote:
After sleeping on it, your other suggestion of TABLE OF, or possibly
TABLE FROM, is starting to grow on me.
Who else has an opinion?
Alright, for my 2c, I like having this syntax
Noah Misch n...@leadboat.com writes:
On Tue, Dec 03, 2013 at 10:03:32AM -0500, Stephen Frost wrote:
Alright, for my 2c, I like having this syntax include 'TABLE' simply
because it's what folks coming from Oracle might be looking for.
Following from that, to keep it distinct from the spec's
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Another issue is that if you are used to the Oracle syntax, in which an
UNNEST() is presumed, it's not exactly clear that TABLE ROWS, or any other
phrase including TABLE, *doesn't* also imply an UNNEST. So to me that's
kind of a strike against Stephen's
On Thu, Nov 21, 2013 at 12:22:57PM -0500, Tom Lane wrote:
Andrew Gierth and...@tao11.riddles.org.uk writes:
If there isn't a reasonable syntax alternative to TABLE(...) for the
multiple functions case, then frankly I think we should go ahead and
burn compatibility with a spec feature which
Noah Misch n...@leadboat.com writes:
That's how I read it, too. My hypothesis is that the standard adopted TABLE()
to rubber-stamp Oracle's traditional name for UNNEST().
Hmm ... plausible.
... I propose merely changing the syntax to TABLE FOR ROWS (...).
Ugh :-(. Verbose and not exactly
On Mon, Dec 02, 2013 at 08:56:03PM -0500, Tom Lane wrote:
Noah Misch n...@leadboat.com writes:
... I propose merely changing the syntax to TABLE FOR ROWS (...).
Ugh :-(. Verbose and not exactly intuitive, I think. I don't like
any of the other options you listed much better. Still, the
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom I've committed this patch after some significant editorialization, but
Tom leaving the use of TABLE( ... ) syntax in-place. If we decide that we
Tom don't want to risk doing that, we can change to some other syntax later.
Is this intended:
Andrew Gierth and...@tao11.riddles.org.uk writes:
Is this intended:
[ I assume you forgot a create type footype here ]
create function foo() returns setof footype language plpgsql
as $f$ begin return next row(1,true); end; $f$;
select pg_typeof(f), row_to_json(f) from foo() with
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom [ I assume you forgot a create type footype here ]
yeah, sorry
Tom Well, it's not insane on its face. The rowtype of f in the
Tom first example is necessarily a built-on-the-fly record, but in
Tom the second case using the properties of the
Andrew Gierth and...@tao11.riddles.org.uk writes:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom Well, it's not insane on its face. The rowtype of f in the
Tom first example is necessarily a built-on-the-fly record, but in
Tom the second case using the properties of the underlying named
I've committed this patch after some significant editorialization, but
leaving the use of TABLE( ... ) syntax in-place. If we decide that we
don't want to risk doing that, we can change to some other syntax later.
regards, tom lane
--
Sent via pgsql-hackers mailing
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom Anyway, after further thought I've come up with an approach
Tom that's purely a syntactic transformation and so less likely to
Tom cause surprise: let's say that if we have TABLE() with a single
Tom argument, and no coldeflist either inside or
Andrew Gierth and...@tao11.riddles.org.uk writes:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom Anyway, after further thought I've come up with an approach
Tom that's purely a syntactic transformation and so less likely to
Tom cause surprise: let's say that if we have TABLE() with a single
David Johnston pol...@yahoo.com writes:
Tom Lane-2 wrote
We could conceivably say that we'll implicitly UNNEST() if the function
returns array, and not otherwise --- but that seems pretty inconsistent
and surprise-making to me.
The use-cases for putting a scalar array returning function
Andrew Gierth and...@tao11.riddles.org.uk wrote:
The spec syntax for table function calls, table function derived table
in table reference, looks like TABLE(func(args...)) AS ...
This patch implements that, plus an extension: it allows multiple
functions, TABLE(func1(...), func2(...),
On Wed, Nov 20, 2013 at 3:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Andrew Gierth and...@tao11.riddles.org.uk wrote:
The spec syntax for table function calls, table function derived table
in table reference, looks like TABLE(func(args...)) AS ...
This patch implements that, plus an extension:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom and this would result in producing the array elements as a table
Tom column. There is nothing in there about a function returning
Tom set.
In the spec, there is no such thing as a function returning a set of
rows in the sense that we use.
Robert Haas wrote
select * from table(array(select generate_series(10,20,5)),
array['fred','jim']);
Can we have our arrays and eat our functions too? (and is someone willing to
bake such a complicated cake...)
select * from table ( ARRAY | FUNCTION/SET [, ARRAY | FUNCTION/SET ]* )
The
Andrew Gierth and...@tao11.riddles.org.uk writes:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom and this would result in producing the array elements as a table
Tom column. There is nothing in there about a function returning
Tom set.
In the spec, there is no such thing as a function
Robert Haas robertmh...@gmail.com writes:
The original post on this thread includes this example, which mixes
SRFs and arrays by running the array through UNNEST:
select * from table(generate_series(10,20,5), unnest(array['fred','jim']));
But if we think the spec calls for things to be
Tom Lane-2 wrote
Andrew Gierth lt;
andrew@.org
gt; writes:
Tom == Tom Lane lt;
tgl@.pa
gt; writes:
Tom and this would result in producing the array elements as a table
Tom column. There is nothing in there about a function returning
Tom set.
In the spec, there is no such thing
David Johnston pol...@yahoo.com writes:
Tom Lane-2 wrote
We could conceivably say that we'll implicitly UNNEST() if the function
returns array, and not otherwise --- but that seems pretty inconsistent
and surprise-making to me. I'm not too sure what to do if a function
returns setof array,
Tom Lane-2 wrote
We could conceivably say that we'll implicitly UNNEST() if the function
returns array, and not otherwise --- but that seems pretty inconsistent
and surprise-making to me.
The use-cases for putting a scalar array returning function call into a
TABLE construct, and NOT wanting
I wrote:
Andrew Gierth and...@tao11.riddles.org.uk writes:
Here is a new patch with the following changes on top of Heikki's
version (all the changes in which I've otherwise kept):
Here is an updated version:
I've been hacking on this patch all day yesterday. What I'm on about at
the moment
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom I've been hacking on this patch all day yesterday. What I'm on
Tom about at the moment is reversing the decision to move range
Tom functions' funccoltypes etc into FuncExpr. That's a bad idea on
Tom the grounds of bloating FuncExpr, but the
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom BTW, the reason we need to store the column count explicitly is
Tom that we have to ignore the added columns if a composite type has
Tom had an ADD COLUMN done to it since the RTE was made. The
Tom submitted patch fails rather nastily in such
Andrew Gierth and...@tao11.riddles.org.uk writes:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom BTW, the reason we need to store the column count explicitly is
Tom that we have to ignore the added columns if a composite type has
Tom had an ADD COLUMN done to it since the RTE was made. The
Andrew Gierth and...@tao11.riddles.org.uk writes:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom I've been hacking on this patch all day yesterday. What I'm on
Tom about at the moment is reversing the decision to move range
Tom functions' funccoltypes etc into FuncExpr. That's a bad idea
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Inlining should already check that the type doesn't change as a
result; where exactly is the issue here?
Tom The issue is that if you want to dig column type information out
Tom of a function RTE, that won't necessarily work after
Tom
Andrew Gierth and...@tao11.riddles.org.uk writes:
Tom == Tom Lane t...@sss.pgh.pa.us writes:
Tom The issue is that if you want to dig column type information out
Tom of a function RTE, that won't necessarily work after
Tom preprocess_expression has had its way with the contained
Tom
I've spent some time reviewing this patch - looks pretty good! I'm not
through yet, but I wanted to post an update. Attached is a new version,
with some modifications I made. Notably:
I added a new struct to hold the per-function executor state - tupdesc,
tuplestore, rowcount and slot -
Heikki == Heikki Linnakangas hlinnakan...@vmware.com writes:
Heikki I've spent some time reviewing this patch - looks pretty
Heikki good! I'm not through yet, but I wanted to post an
Heikki update. Attached is a new version, with some modifications I
Heikki made. Notably:
Heikki I
2013-09-13 21:03 keltezéssel, Andrew Gierth írta:
Latest version of patch, incorporating regression tests and docs, and
fixing the operator issue previously raised.
It looks good. I think it's ready for a committer.
Best regards,
Zoltán Böszörményi
--
--
On Wed, Aug 28, 2013 at 12:09:05AM -0400, Peter Eisentraut wrote:
On Tue, 2013-08-27 at 09:44 -0400, Tom Lane wrote:
Boszormenyi Zoltan z...@cybertec.at writes:
When adding regression tests, can you please add intentional
syntax error cases to exercise all the new ereport()s?
Please
2013-08-27 01:24 keltezéssel, Andrew Gierth írta:
Latest version of patch. This should be it as far as code goes; there
may be some more regression test work, and a doc patch will be
forthcoming.
This version supports, in addition to the previous stuff:
[snip]
In my limited testing, it works
Boszormenyi Zoltan z...@cybertec.at writes:
When adding regression tests, can you please add intentional
syntax error cases to exercise all the new ereport()s?
Please do not add test cases merely to prove that. Yeah, you should
probably have exercised each error case in devel testing, but that
On Tue, 2013-08-27 at 09:44 -0400, Tom Lane wrote:
Boszormenyi Zoltan z...@cybertec.at writes:
When adding regression tests, can you please add intentional
syntax error cases to exercise all the new ereport()s?
Please do not add test cases merely to prove that. Yeah, you should
probably
On Mon, 2013-08-26 at 23:24 +, Andrew Gierth wrote:
Latest version of patch. This should be it as far as code goes; there
may be some more regression test work, and a doc patch will be
forthcoming.
In src/include/optimizer/paths.h, you are using operator as a function
argument name, which
On Tue, 2013-08-13 at 13:54 +, Andrew Gierth wrote:
Summary:
This patch implements a method for expanding multiple SRFs in parallel
that does not have the surprising LCM behaviour of SRFs-in-select-list.
(Functions returning fewer rows are padded with nulls instead.)
Fails to build in
On 08/20/2013 02:03 AM, Josh Berkus wrote:
On 08/19/2013 09:23 AM, Boszormenyi Zoltan wrote:
Indeed, it's a big nail in the coffin for SRFs-in-targetlist. Having
WITH ORDINALITY and this feature, I would vote for removing
SRF-in-targetlist and call the release PostgreSQL 10.0.
That's not
On Mon, Aug 19, 2013 at 07:45:23PM +0200, Pavel Stehule wrote:
Hello
Harder maybe but it may still be cleaner in the long run.
Overall, it's my intention here to remove as many as feasible of the old
reasons why one might use an SRF in the select list.
Indeed, it's a big nail in
2013/8/20 David Fetter da...@fetter.org
On Mon, Aug 19, 2013 at 07:45:23PM +0200, Pavel Stehule wrote:
Hello
Harder maybe but it may still be cleaner in the long run.
Overall, it's my intention here to remove as many as feasible of the
old
reasons why one might use an SRF in the
2013-08-20 08:13 keltezéssel, Pavel Stehule írta:
2013/8/20 David Fetter da...@fetter.org mailto:da...@fetter.org
On Mon, Aug 19, 2013 at 07:45:23PM +0200, Pavel Stehule wrote:
Hello
Harder maybe but it may still be cleaner in the long run.
Overall, it's my
Hi,
2013-08-13 15:54 keltezéssel, Andrew Gierth írta:
Summary:
This patch implements a method for expanding multiple SRFs in parallel
that does not have the surprising LCM behaviour of SRFs-in-select-list.
(Functions returning fewer rows are padded with nulls instead.)
It then uses this
Hello
Harder maybe but it may still be cleaner in the long run.
Overall, it's my intention here to remove as many as feasible of the old
reasons why one might use an SRF in the select list.
Indeed, it's a big nail in the coffin for SRFs-in-targetlist. Having
WITH ORDINALITY and this
On 08/19/2013 09:23 AM, Boszormenyi Zoltan wrote:
Indeed, it's a big nail in the coffin for SRFs-in-targetlist. Having
WITH ORDINALITY and this feature, I would vote for removing
SRF-in-targetlist and call the release PostgreSQL 10.0.
That's not realistic. We'd have to deprecate that
2013-08-19 20:03 keltezéssel, Josh Berkus írta:
On 08/19/2013 09:23 AM, Boszormenyi Zoltan wrote:
Indeed, it's a big nail in the coffin for SRFs-in-targetlist. Having
WITH ORDINALITY and this feature, I would vote for removing
SRF-in-targetlist and call the release PostgreSQL 10.0.
That's not
Boszormenyi Zoltan wrote:
This parser hackery is of course somewhat ugly. But given the objective
of implementing the spec's unnest syntax, it seems to be the least ugly
of the possible approaches. (The hard part of doing it any other way
would be generating the description of the result type;
2013-08-19 22:04 keltezéssel, Andrew Gierth írta:
Boszormenyi Zoltan wrote:
This parser hackery is of course somewhat ugly. But given the objective
of implementing the spec's unnest syntax, it seems to be the least ugly
of the possible approaches. (The hard part of doing it any other way
would
On 08/13/2013 06:54 AM, Andrew Gierth wrote:
Summary:
This patch implements a method for expanding multiple SRFs in parallel
that does not have the surprising LCM behaviour of SRFs-in-select-list.
(Functions returning fewer rows are padded with nulls instead.)
BTW, if anyone is unsure of
On 08/14/2013 08:22 AM, Josh Berkus wrote:
On 08/13/2013 06:54 AM, Andrew Gierth wrote:
Summary:
This patch implements a method for expanding multiple SRFs in parallel
that does not have the surprising LCM behaviour of SRFs-in-select-list.
(Functions returning fewer rows are padded with
On Sat, Nov 20, 2010 at 01:54:32PM +0900, Itagaki Takahiro wrote:
On Sat, Nov 20, 2010 at 03:48, caleb.wel...@emc.com wrote:
Note the standard also supports unnesting multiple arrays concurrently, the
rule for handling arrays with different lengths is to use null padding of
the shorter
Note the standard also supports unnesting multiple arrays concurrently, the
rule for handling arrays with different lengths is to use null padding of the
shorter array.
SELECT * FROM
UNNEST( ARRAY[5,2,3,4],
ARRAY['hello', 'world'] )
WITH ORDINALITY AS t(a,b,i);
a b i
On Fri, Nov 19, 2010 at 01:48:06PM -0500, caleb.wel...@emc.com wrote:
Note the standard also supports unnesting multiple arrays
concurrently, the rule for handling arrays with different lengths is
to use null padding of the shorter array.
Interesting. I notice that our version doesn't support
Excerpts from Caleb.Welton's message of vie nov 19 15:48:06 -0300 2010:
Note the standard also supports unnesting multiple arrays concurrently, the
rule for handling arrays with different lengths is to use null padding of the
shorter array.
SELECT * FROM
UNNEST( ARRAY[5,2,3,4],
The other aspect of the standard that the Postgres implementation does not
currently support is the fact that unnest is supposed to be defined in terms of
laterally derived subqueries, e.g. you should be able to unnest another element
from a from list entry laterally on the left.
CREATE TABLE
On Fri, Nov 19, 2010 at 04:11:56PM -0500, caleb.wel...@emc.com wrote:
The other aspect of the standard that the Postgres implementation
does not currently support is the fact that unnest is supposed to be
defined in terms of laterally derived subqueries, e.g. you should be
able to unnest
On Sat, Nov 20, 2010 at 03:48, caleb.wel...@emc.com wrote:
Note the standard also supports unnesting multiple arrays concurrently, the
rule for handling arrays with different lengths is to use null padding of the
shorter array.
UNNEST( ARRAY[5,2,3,4],
ARRAY['hello', 'world'] )
Folks,
I'd like to see about getting an enhanced UNNEST, first the one
according to the SQL standard, namely with an optional WITH ORDINALITY
clause, and possibly some extra enhancements.
In order to get WITH ORDINALITY, would it be better to change gram.y
to account for both WITH ORDINALITY and
On Fri, Nov 19, 2010 at 08:33, David Fetter da...@fetter.org wrote:
In order to get WITH ORDINALITY, would it be better to change gram.y
to account for both WITH ORDINALITY and without, or just for the WITH
ORDINALITY case?
We probably need to change gram.y and make UNNEST to be
On Fri, Nov 19, 2010 at 11:40:05AM +0900, Itagaki Takahiro wrote:
On Fri, Nov 19, 2010 at 08:33, David Fetter da...@fetter.org wrote:
In order to get WITH ORDINALITY, would it be better to change
gram.y to account for both WITH ORDINALITY and without, or just
for the WITH ORDINALITY case?
Bruce Momjian wrote:
I assume this is not something for our PostgreSQL CVS, even the later
SRF implementation.
I agree with that assessment, at least in its present state. For example:
regression=# select * from unnest(array[[1,2,3],[4,5,6]]);
unnest
1
2
3
4
Joe Conway [EMAIL PROTECTED] writes:
Problem is that a polymorphic SRF cannot (currently at least) both
accept and return type anyarray.
Beyond that, would the proposed function really be SQL-compliant other
than this one point? I had the idea that UNNEST required some
fundamental changes
I assume this is not something for our PostgreSQL CVS, even the later
SRF implementation.
---
John Hansen wrote:
Attached, array - rows iterator.
select * from unnest(array[1,2,3,4,5]);
Unnest
---
1
On Nov 5, 2004, at 7:09 AM, John Hansen wrote:
Attached, array - rows iterator.
select * from unnest(array[1,2,3,4,5]);
This is really handy! But there is a problem...
The switch statement could probably be done in a different way, but
there doesn't seem to be any good examples of how to return
On Fri, 5 Nov 2004, John Hansen wrote:
Attached, array - rows iterator.
select * from unnest(array[1,2,3,4,5]);
Unnest
---
1
2
3
4
5
5 rows
This mechanism is actually designed for the multiset data type in SQL.
AFAICT, our elementary one dimensional array handling
The switch statement could probably be done in a different way, but
there doesn't seem to be any good examples of how to return anyitem. If
anyone have a better way, please let me know.
Why do you need the switch statement at all? array-elements is already
an array of Datums. Won't
Attached, array - rows iterator.
select * from unnest(array[1,2,3,4,5]);
Unnest
---
1
2
3
4
5
5 rows
The switch statement could probably be done in a different way, but
there doesn't seem to be any good examples of how to return anyitem. If
anyone have a better way, please let
On Fri, 5 Nov 2004, John Hansen wrote:
Does anyone know how to check individual array elements for NULL values?
PG_ARG_ISNULL() seems to return true if ANY array element is null; ex::
array[1,2,3,null,4,5]
Arrays cannot store NULL elements, check your above statement and see that
the whole
77 matches
Mail list logo
