Thread: TODO item
In the TODO list there is an item "[D] Completed itemAdd array_agg() and UNNEST functions for arrays " marked as done but 5 items below there is: "Add SQL-standard array_agg() and unnest() array functions " it's the same item so this one should be removed... or there is a difference between the array_agg() and unnest() implemented versus SQL-standard array_agg() and unnest()? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
>>>>> "Jaime" == Jaime Casanova <jcasanov@systemguards.com.ec> writes: Jaime> In the TODO list there is an item "[D] Completed itemAddJaime> array_agg() and UNNEST functions for arrays " markedas doneJaime> but 5 items below there is: "Add SQL-standard array_agg() andJaime> unnest() array functions " it's thesame item so this oneJaime> should be removed... or there is a difference between theJaime> array_agg() and unnest() implementedversus SQL-standardJaime> 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 aboutBruce> what additional functionality we need once we getuserBruce> 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 - withoutit, 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 outsidethe subquery at the samelevel. For example, a LATERAL subquery in aFROM clause could reference tables defined in the same FROM clause.Currentlyonly the columns of tables defined above subqueries arerecognized. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +