Greg,
this is brilliant - thank you very much!
Is "partition by" compatible to PostgreSQL 8.0/8.2? I could not find compatibility information. It works fine with PG 8.3/8.4 and Greenplum 3.3 thou.
Konstantin
On Tue, Nov 24, 2009 at 6:30 PM, Greg Stark
<gsstark@mit.edu> wrote:
On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <
pgfizm@gmail.com> wrote:
> My question: can pg_attribute.attnum be used to determine the sequential
> ordinal positions of columns in a table? What is a right way to get the
> ordinal numbers?
row_number() over (partition by T.schemaname,T.viewname order by
attnum) as "ORDINAL_POSITION"
If you just stick this in there in place of attnum it'll cause an
extra sort. It should be possible with enough clever rearranging of
the query to do the whole query with a single sort since that's the
same sort order that the results are ordered in.
Incidentally you probably want UNION ALL rather than UNION in the
original query.
--
greg