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 <gsst...@mit.edu> wrote: > On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <pgf...@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? > > You could use something like: > > 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 >
