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

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

Thanks,
Matt

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.