can i use an array as a table (in the from clause)

On Aug 13, 2007, at 9:25 , Coarr, Matt wrote:

Is there some way that I can treat a two dimensional array as a table
that can be referenced in the from clause?

I know of no way off hand, and if so, not easily. This is a pretty
clear sign that you shouldn't be using arrays in this context and
should rethink your schema.

Michael Glaesemann
grzm seespotcode net

--- Michael Glaesemann <grzm@seespotcode.net> wrote:

Is there some way that I can treat a two dimensional array as a table
that can be referenced in the from clause?

I know of no way off hand, and if so, not easily. This is a pretty
clear sign that you shouldn't be using arrays in this context and
should rethink your schema.

The closest thing to an array (but is actually a derived table) is achieved using the VALUES
predicate.

SELECT col1, avg( col2 )
FROM ( VALUES ( 1, 1 ), ( 1, 2 ),
( 2, 3 ), ( 2, 4 )) AS ValuesTable( col1, col2 )
WHERE col2 < 5
GROUP BY col1
ORDER BY col1;

col1 | avg
------+--------------------
1 | 1.5000000000000000
2 | 3.5000000000000000
(2 rows)

Regards,
Richard Broersma Jr.