Re: pg_attribute.attnum - wrong column ordinal?

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?

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