Hi,

In PostgreSQL > 8.4, you can say:

SELECT * from UNNEST(ARRAY[1, 2, 3]);

And you get back three rows, each containing a single number.

Is this UNNEST function from an SQL standard, or is it something the
PostgreSQL guys made up? I've googled a bit but been unable to find an
answer.

Whether it's standard or not, is it something which appears in other
databases? I've googled, and it seems to be in DB2, but i can't tell if
it's in Oracle or not, and i didn't see anything concerning any other
database.

I'm trying to write a moderately funky query, and it would really help if
i could use UNNEST, but i'd like to stick to portable SQL as far as
possible. I'm using PostgreSQL at the moment, but would really like this
to work with other databases, in particular Oracle.

Thanks,
tom

--
drank lots of pints of beer, usually grolsch/met friends/museums/watch
tele/read papers/thought a lot/walked much/much tube (no accidents)/some
burgers/some pizza/some resturants (the ones I could afford)/some english
breakfasts/some puddings -- Dor Zaf, 15 days in the UK

Tom Anderson <twic (AT) urchin (DOT) earth.li> writes:

It's a damned shame that the official SQL specification isn't online for
public inspection and reference to quickly answer questions like that.

The best I've been able to do is to see that PostgreSQL 8.4 docs have
<URL:http://www.postgresql.org/docs/8.4/static/unsupported-features-sql-standard.html>
listing “S301 Enhanced UNNEST� as an unsupported feature from the
standard.

That strongly implies that UNNEST *is* specified in standard SQL, since
the PostgreSQL 8.4 docs are noting that a specified enhancement is not
available in their implementation.

You might have success looking for equivalent documentation to
PostgreSQL's “Supported features� and “Unsupported features� lists in
the corresponding documentation for each vendor's implementation. Your
limit, then, is how well that information is documented; but I'd be
surprised if, for example, Oracle's documentation doesn't have such a
list.

--
\ “Good judgement comes from experience. Experience comes from |
`\ bad judgement.� —Frederick P. Brooks |
_o__) |
Ben Finney

On 2010-05-09 03:35, Ben Finney wrote:
Though not final, the late drafts are usually good enough:

http://www.wiscorp.com/sql200n.zip

Quote: This points to the documents which wlll likely be the documents
that represent the SQL 2008 Standard. These documents are out for
International Standard ballot at this time. The vote is an Up/Down vote.
No changes allowed.


There are references to the unnest operation in
5CD2-02-Foundation-2006-01.pdf, I did not check whether the usage in
op's post conforms to those though.

/Lennart

On Sun, 9 May 2010, Lennart Jonsson wrote:

Wonderful! These now occupy pride of place in my documentation folder.

The weird thing is that i found lots of uses of UNNEST (in defining how
casts work and various other things), but nowhere that actually defined
what UNNEST does. Is there a file missing from that collection?

FWIW, H2 doesn't support UNNEST. It does have something similar in the
shape of a TABLE pseudo-function, which lets you create temporary tables
inline, and into which you can substitute array parameters. In JDBC
syntax:

SELECT * FROM TABLE(x INTEGER = ?);

You can put an array in as the parameter. Although H2 doesn't support the
java.sql.Array type; you have to use a normal java array instead. And you
can't use TABLE with IN; this:

SELECT * FROM thing WHERE thing_id IN TABLE(selected_id INTEGER = ?);

doesn't work. You have to rewrite it as a join:

SELECT * FROM thing, TABLE(selected_id INTEGER = ?) WHERE thing_id = selected_id;

Which is a real pain if you want to use this inside a subquery or
something.

tom

--
I don't wanna know your name, i just want BANG BANG BANG!

[x-posted to comp.lang.java.programmer, since this is about JDBC and a
java embedded database]

For the benefit of cljp readers, UNNEST is an SQL-standard function which
takes an array value and returns a table-like value, which you can use in
join and IN conditions; i want it because i can use it to write queries
that are like "select everything with an ID in this set".

On Sun, 9 May 2010, Tom Anderson wrote:

Correction - you can also whip up a closer approximation of UNNEST, which
can be used in an IN clause, by hand:

SELECT * FROM thing WHERE thing_id IN (SELECT ARRAY_GET(?1, X) AS selected_id FROM SYSTEM_RANGE(1, ARRAY_LENGTH(?1)));

(the AS is unnecessary but informative)

That makes a 100% genuine subquery, so i'd be optimistic about the query
planner doing something sensible here.

tom

--
When the facts change, I change my mind. What do you do, sir? -- John
Maynard Keynes

Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> writes:

Interesting, thank you.

How does that organisation get ahold of those documents? What is the
difference in copyright license between these “late drafts� and the
final specification? If an arbitrary person were to redistribute the
drafts, is that a violation of the copyright?

My understanding is that SQL 2011 drafts are currently being discussed.
Are they likely to be made available also, and what is the copyright
status of them?

--
\ “From the moment I picked your book up until I laid it down I |
`\ was convulsed with laughter. Someday I intend reading it.� |
_o__) —Groucho Marx |
Ben Finney

On 09-05-2010 14:14, Tom Anderson wrote:
I believe that UNNEST requires SQL99.

Lots of database only fully support SQL92.

Arne

On 2010-05-10 02:24, Ben Finney wrote:
[...]
I don't know for sure, but I assume it has something to do with:

http://www.wiscorp.com/about_wiscorp.html
<Quote>
[...]
Whitemarsh's founder, Michael M. Gorman, has been involved in database
full-time since 1969. Mr. Gorman, is a charter member of X3H2. X3H2 is
the American National Standards Institute (ANSI) National Committee on
Information Technology Standards (NCITS) H2 Technical Committee on
Database, and has been X3H2’s secretary since 1978.

My guess is that the draft docs are available as part of the voting
process. I'm pretty sure I have seen draft documents elsewhere as well,
but I can't seem to find a reference now.

I don't know this either, I can only hope that they become available. It
is not that often, but every now and then there is something worth
investigating.


/Lennart