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 appears to be of negative > > value. > > TBH, I'm getting close to that conclusion too. The more I look at the > spec, the more I think it must be a mistake, or else I'm somehow reading > it wrong, because it sure makes no sense for them to have invented > something that's just an alternative and less-clear syntax for a feature > they already had. > > Can anyone who's following this thread check the behavior of Oracle or > DB2 to see if they interpret TABLE() the way I think the spec says?
Oracle's closest analog to SQL-standard arrays is its "varray" feature, and TABLE() behaves like our UNNEST() for those. Note that Oracle has no UNNEST. *SQL> CREATE OR REPLACE TYPE intarray AS VARRAY(100) OF int; * 2 / Type created. *SQL> select * from table(intarray(1,2,3)); COLUMN_VALUE ------------ 1 2 3 I don't have a DB2 installation within reach, but its documentation implies that UNNEST and TABLE are interchangeable: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0055064.html If anyone can test "select x from table(trim_array(array[1,2,3], 1)) as t(x);" in DB2 and provide the output, that would be helpful. On Thu, Nov 21, 2013 at 10:07:53AM -0500, Tom Lane wrote: > The whole business with the spec's reading of TABLE() seems bizarre. > AFAICS there is nothing about TABLE(foo()) that you can't get with > greater clarity by writing UNNEST(foo()) instead. And it's not like > it's a legacy feature --- SQL99 has single-argument UNNEST() but not > TABLE(), so why'd they add TABLE() later, and why'd they make it a > strict subset of what UNNEST() can do? I can't escape the suspicion > that I'm misreading the spec somehow ... but the text seems perfectly > clear. That's how I read it, too. My hypothesis is that the standard adopted TABLE() to rubber-stamp Oracle's traditional name for UNNEST(). On Wed, Nov 20, 2013 at 03:07:17PM -0500, Tom Lane wrote: > I do like the basic concept of this syntax, but I think it's a serious > error to appropriate the TABLE() spelling for something that doesn't > agree with the spec's semantics for that spelling. We need to spell it > some other way. I realize you may have changed your mind later in the thread, but I share this original sentiment. I think of this feature as optimization of and syntactic sugar for full outer joins on ordinality columns. Compare these queries: select * from table(generate_series(1,3), generate_series(2,5)) with ordinality as t(g1,g2); select g1, g2, ordinality from generate_series(1,3) with ordinality as g1 full join generate_series(2,5) with ordinality as g2 using (ordinality); The new syntax is limited to function calls, but I could imagine extending it to take arbitrary subqueries (or, at the cost of inviting folks to depend on subject-to-change row order, arbitrary from_item's). If this project were just starting, I'd probably favor optimizing ordinality joins in the planner rather than introducing special syntax to request the optimization. I don't claim that's sufficiently better to justify the extensive rework it would now entail, though. Therefore, I propose merely changing the syntax to "TABLE FOR ROWS (...)". As a comparison, think of the standard syntax as "TABLE [FOR ELEMENTS] (...)". Here is a longer list of conflict-free syntax choices that I considered before settling on that one: FUNCTIONS TABLE FUNCTIONS TO TABLE ROWS FOR ROWS FOR EACH ROWS FROM ROWS FROM EACH ROWS FROM FUNCTIONS ROWS TO TABLE TABLE (ROWS OF f0(), ROWS OF f1()) TABLE BY FUNCTIONS TABLE BY ROW TABLE FOR TABLE FOR FUNCTION ROWS TABLE FOR FUNCTIONS TABLE FOR ROWS TABLE FOR ROWS OF TABLE FROM TABLE FROM FUNCTION ROWS TABLE FROM FUNCTIONS TABLE OF TABLE OF EACH TABLE OF FUNCTION ROWS TABLE OF FUNCTIONS TABLE OF ROWS TABLE OF ROWS OF EACH Thanks, nm -- Noah Misch EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers