TODO item

"Jaime" == Jaime Casanova <jcasanov@systemguards.com.ec> writes:

Jaime> In the TODO list there is an item "[D] Completed itemAdd
Jaime> array_agg() and UNNEST functions for arrays " marked as done
Jaime> but 5 items below there is: "Add SQL-standard array_agg() and
Jaime> unnest() array functions " it's the same item so this one
Jaime> should be removed... or there is a difference between the
Jaime> array_agg() and unnest() implemented versus SQL-standard
Jaime> array_agg() and unnest()?

The array_agg() does, I believe, match the standard one, at least
my reading of the spec doesn't reveal any obvious issues there.

The unnest() implementation is largely unrelated to the standard one,
which is impossible to provide without LATERAL.

--
Andrew (irc:RhodiumToad)

Andrew Gierth wrote:

"Jaime" == Jaime Casanova <jcasanov@systemguards.com.ec> writes:

Jaime> In the TODO list there is an item "[D] Completed itemAdd
Jaime> array_agg() and UNNEST functions for arrays " marked as done
Jaime> but 5 items below there is: "Add SQL-standard array_agg() and
Jaime> unnest() array functions " it's the same item so this one
Jaime> should be removed... or there is a difference between the
Jaime> array_agg() and unnest() implemented versus SQL-standard
Jaime> array_agg() and unnest()?

The array_agg() does, I believe, match the standard one, at least
my reading of the spec doesn't reveal any obvious issues there.

The unnest() implementation is largely unrelated to the standard one,
which is impossible to provide without LATERAL.

I removed the duplicate item; we can add more details about what
additional functionality we need once we get user feedback.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

"Bruce" == Bruce Momjian <bruce@momjian.us> writes:

The unnest() implementation is largely unrelated to the standard
one, which is impossible to provide without LATERAL.

Bruce> I removed the duplicate item; we can add more details about
Bruce> what additional functionality we need once we get user
Bruce> feedback.

The missing functionality from the spec is:

1) select ... from foo, unnest(foo.bar); -- UNNEST is implicitly LATERAL

2) multiple arrays: select * from unnest(a,b);

3) expansion of composite arrays: unnest(a) should return as many
columns as there are in the elements of a, not just one composite
column

4) WITH ORDINALITY - adds a column to the result with the array index

It's point (1) that's the killer - without it, unnest() is just a
trivial shorthand for stuff that can be done anyway; it doesn't
actually add any functionality.

--
Andrew.

Andrew Gierth wrote:

"Bruce" == Bruce Momjian <bruce@momjian.us> writes:

The unnest() implementation is largely unrelated to the standard
one, which is impossible to provide without LATERAL.

Bruce> I removed the duplicate item; we can add more details about
Bruce> what additional functionality we need once we get user
Bruce> feedback.

The missing functionality from the spec is:

1) select ... from foo, unnest(foo.bar); -- UNNEST is implicitly LATERAL

2) multiple arrays: select * from unnest(a,b);

3) expansion of composite arrays: unnest(a) should return as many
columns as there are in the elements of a, not just one composite
column

4) WITH ORDINALITY - adds a column to the result with the array index

It's point (1) that's the killer - without it, unnest() is just a
trivial shorthand for stuff that can be done anyway; it doesn't
actually add any functionality.

OK, so what should the TODO wording be?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sat, 2009-03-28 at 11:57 +0000, Andrew Gierth wrote:

The array_agg() does, I believe, match the standard one, at least
my reading of the spec doesn't reveal any obvious issues there.

I think it's missing the ORDER BY clause. This is not as important for
PostgreSQL because we can do ORDER BY in a subselect, but it's still a
deviation from the standard.

Regards,
Jeff Davis

"Jeff" == Jeff Davis <pgsql@j-davis.com> writes:

On Sat, 2009-03-28 at 11:57 +0000, Andrew Gierth wrote:

The array_agg() does, I believe, match the standard one, at least
my reading of the spec doesn't reveal any obvious issues there.

Jeff> I think it's missing the ORDER BY clause.

Hm, yeah, so it is.

Could that be added (not for 8.4, and not necessarily just for
array_agg but for all aggregates) by piggybacking on the existing
DISTINCT mechanism for aggregates?

--
Andrew.

"Bruce" == Bruce Momjian <bruce@momjian.us> writes:

1) select ... from foo, unnest(foo.bar); -- UNNEST is implicitly LATERAL

[...]

It's point (1) that's the killer - without it, unnest() is just a
trivial shorthand for stuff that can be done anyway; it doesn't
actually add any functionality.

Bruce> OK, so what should the TODO wording be?

Under "SQL Commands":

* implement LATERAL (and corresponding UNNEST functionality)

(LATERAL is, I suspect, a fairly big project because of the amount of
planner work involved, but it's also a fairly high-value project
because (a) it's useful (we usually get a couple of cases every week
on the IRC chan where people ask "how do I do X", where X would be
trivial with LATERAL but requires complex and often inefficient SQL
without it), and (b) it potentially presents optimization
opportunities even for queries that don't use it.)

--
Andrew.

On Sat, 2009-03-28 at 15:35 +0000, Andrew Gierth wrote:

"Jeff" == Jeff Davis <pgsql@j-davis.com> writes:

On Sat, 2009-03-28 at 11:57 +0000, Andrew Gierth wrote:

The array_agg() does, I believe, match the standard one, at least
my reading of the spec doesn't reveal any obvious issues there.

Jeff> I think it's missing the ORDER BY clause.

Hm, yeah, so it is.

Could that be added (not for 8.4, and not necessarily just for
array_agg but for all aggregates) by piggybacking on the existing
DISTINCT mechanism for aggregates?

I'm sure it's possible, but it seems like a significant amount of work.
I don't feel very strongly about it myself, because, as I said, it can
be worked around using an ORDER BY in a subselect.

Regards,
Jeff Davis

Andrew Gierth wrote:

"Bruce" == Bruce Momjian <bruce@momjian.us> writes:

1) select ... from foo, unnest(foo.bar); -- UNNEST is implicitly LATERAL

[...]

It's point (1) that's the killer - without it, unnest() is just a
trivial shorthand for stuff that can be done anyway; it doesn't
actually add any functionality.

Bruce> OK, so what should the TODO wording be?

Under "SQL Commands":

* implement LATERAL (and corresponding UNNEST functionality)

(LATERAL is, I suspect, a fairly big project because of the amount of
planner work involved, but it's also a fairly high-value project
because (a) it's useful (we usually get a couple of cases every week
on the IRC chan where people ask "how do I do X", where X would be
trivial with LATERAL but requires complex and often inefficient SQL
without it), and (b) it potentially presents optimization
opportunities even for queries that don't use it.)

Added to TODO:

Support LATERAL subqueries

Lateral subqueries can reference columns of tables defined outside
the subquery at the same level. For example, a LATERAL subquery in a
FROM clause could reference tables defined in the same FROM clause.
Currently only the columns of tables defined above subqueries are
recognized.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +