On Thu, Dec 05, 2013 at 10:34:08PM -0500, Stephen Frost wrote:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > Noah Misch 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
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Noah Misch 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
Noah Misch 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 pgsql-ha
On Tue, Dec 03, 2013 at 02:27:06PM -0500, Tom Lane wrote:
> Noah Misch 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.
> >> Fol
* 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
Noah Misch 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 notion of
>> '
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 s
2013/12/3 Stephen Frost
> * 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
* 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
Noah Misch 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 single
>> wo
On Mon, Dec 2, 2013 at 11:26 PM, Noah Misch wrote:
> On Mon, Dec 02, 2013 at 08:56:03PM -0500, Tom Lane wrote:
>> Noah Misch 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 oth
On Mon, Dec 02, 2013 at 08:56:03PM -0500, Tom Lane wrote:
> Noah Misch 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 idea of
> u
Noah Misch 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 intuitive, I t
On Thu, Nov 21, 2013 at 12:22:57PM -0500, Tom Lane wrote:
> Andrew Gierth 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 appears to be of nega
Andrew Gierth writes:
> "Tom" == Tom Lane 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
> Tom> composite type is possible, and
> "Tom" == Tom Lane 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 underlyin
Andrew Gierth 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 ordinality f(p,q);
> pg_typeof |
> "Tom" == Tom Lane 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:
create funct
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 list
David Johnston 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 call into a
Andrew Gierth writes:
> "Tom" == Tom Lane 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
> "Tom" == Tom Lane 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 outside,
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 wantin
David Johnston 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, either.
Tom Lane-2 wrote
> Andrew Gierth <
> andrew@.org
> > writes:
>> "Tom" == Tom Lane <
> tgl@.pa
> > 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
Robert Haas 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 implicitly unnested,
>
Andrew Gierth writes:
> "Tom" == Tom Lane 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
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 standa
> "Tom" == Tom Lane 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.
Functions can
On Wed, Nov 20, 2013 at 3:07 PM, Tom Lane wrote:
> Andrew Gierth wrote:
>> The spec syntax for table function calls,
>> in , looks like TABLE(func(args...)) AS ...
>
>> This patch implements that, plus an extension: it allows multiple
>> functions, TABLE(func1(...), func2(...), func3(...)) [WITH
Andrew Gierth wrote:
> The spec syntax for table function calls,
> in , looks like TABLE(func(args...)) AS ...
> This patch implements that, plus an extension: it allows multiple
> functions, TABLE(func1(...), func2(...), func3(...)) [WITH ORDINALITY]
> and defines this as meaning that the funct
Andrew Gierth writes:
> "Tom" == Tom Lane 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> expressions. That's needed at the very
> "Tom" == Tom Lane 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> preprocess_expr
Andrew Gierth writes:
> "Tom" == Tom Lane 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 Fu
Andrew Gierth writes:
> "Tom" == Tom Lane 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
> "Tom" == Tom Lane 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 cases, if
> "Tom" == Tom Lane 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 real probl
I wrote:
> Andrew Gierth 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 is reversing the deci
> "Heikki" == Heikki Linnakangas 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 refactored the gramm
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 - instea
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
--
--
Zolt
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 writes:
> > > When adding regression tests, can you please add intentional
> > > syntax error cases to exercise all the new ereport()s?
> >
> > Please do n
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, wh
On Tue, 2013-08-27 at 09:44 -0400, Tom Lane wrote:
> Boszormenyi Zoltan 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 exer
Boszormenyi Zoltan 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 does
not mean
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
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
2013-08-20 08:13 keltezéssel, Pavel Stehule írta:
2013/8/20 David Fetter 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 intention h
2013/8/20 David Fetter
> 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 th
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
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.
>
> Th
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 b
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 resul
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 r
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 sy
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 th
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 method
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
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 o
On Sat, Nov 20, 2010 at 01:54:32PM +0900, Itagaki Takahiro wrote:
> On Sat, Nov 20, 2010 at 03:48, 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.
> >
> >
On Sat, Nov 20, 2010 at 03:48, 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'] )
> WITH ORDIN
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 an
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 t
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],
>
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 suppo
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 11:40:05AM +0900, Itagaki Takahiro wrote:
> On Fri, Nov 19, 2010 at 08:33, David Fetter 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 pro
On Fri, Nov 19, 2010 at 08:33, David Fetter 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 COL_NAME_KEYWORD.
UNNEST (wi
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
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
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
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
> ---
> > 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.
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
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 a
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 who
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
77 matches
Mail list logo
