Re: 'order by' in an insert into command

Richard Huxton <dev(at)archonet(dot)com> writes:
> In your example, I would expect the nextval() to be called during the
> "fetch", before the ordering. You could probably do something like:

> INSERT INTO pending_tnmt_sec
> SELECT foo.*, nextval('sec_seq') FROM
> (
> SELECT tseceventid, ...
> ORDER BY tsecrtddt,tseceventid,tsecsecno
> ) AS foo
> ;

> I'm not sure whether the SQL standard requires the ORDER BY to be
> processed in the sub-select. From a relational viewpoint, I suppose you
> could argue that ordering is strictly an output feature.

I believe the SQL standard disallows this entirely, precisely because it
considers ordering to be strictly an output feature. Postgres will take
it though (in recent releases), and should produce the results Mike wants.

regards, tom lane