Re: pg_attribute.attnum - wrong column ordinal?

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(at)mit(dot)edu> wrote:

> On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)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
>