Lists: | pgsql-general |
---|
From: | "Coarr, Matt" <mcoarr(at)mitre(dot)org> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | can i use an array as a table (in the from clause) |
Date: | 2007-08-13 14:25:07 |
Message-ID: | CFAEF46E71B77E44B6E83750F195BF2701668029@IMCSRV4.MITRE.ORG |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | "Coarr, Matt" <mcoarr(at)mitre(dot)org> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: can i use an array as a table (in the from clause) |
Date: | 2007-08-13 16:08:51 |
Message-ID: | CACEA317-6255-445D-AF0A-B376EB4591E8@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | Michael Glaesemann <grzm(at)seespotcode(dot)net>, "Coarr, Matt" <mcoarr(at)mitre(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: can i use an array as a table (in the from clause) |
Date: | 2007-08-13 16:32:48 |
Message-ID: | 479710.12327.qm@web31813.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
--- Michael Glaesemann <grzm(at)seespotcode(dot)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.