Order in CREATE VIEW

"Marcin Wasilewski" wrote:

hello everybody,
Can you help me?

I have POSTGRESQL 7.0.3,
I try to create simple view by typing.

create view "xx" as select "aa.yy", "bb.yy" from "yy" order by "bb.yy"

the problem is that parameter order is not implemented with create view.
so how can I create such simple query??

A view creates a virtual table; there is no implicit ordering in a table,
so it follows that you should not be able to impose one in a view.

You seem to want to create a stored query, so why not use pgaccess to do
just that?

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Be strong, and let your heart take courage, all you
who hope in the Lord." Psalm 31:24

On Wed, Apr 11, 2001 at 10:25:24AM +0200, Marcin Wasilewski wrote:

hello everybody,
Can you help me?

I have POSTGRESQL 7.0.3,
I try to create simple view by typing.

create view "xx" as select "aa.yy", "bb.yy" from "yy" order by "bb.yy"

the problem is that parameter order is not implemented with create view.
so how can I create such simple query??

unless your table "yy" has fields "aa.yy" and "bb.yy" (or
something like "first name" or "client's phone") i'd start by
revising that to something like:

create view
"xx" as
select
"aa"."yy",
"bb"."yy"
from
"yy"
order by
"bb"."yy"
;

but even there you still have problems:

table aa field yy
table bb field yy
-> the names will collide (two fields named yy in the view)

plus, there's no definition of how the two tables relate to each
other.

not knowing what your example is based on, how about something
like this...

create view
xx as
-- and here comes a regular old SELECT statement:
select
aa.yy as aa_yy_or_whatever,
bb.yy as bb_yy_you_get_the_idea
from
aa,
bb
where
aa.somefield = bb.otherfield
order by
bb.yy
;

--
americans should never read anything so subversive as what's at
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

"Oliver Elphick" <olly@lfix.co.uk> writes:

A view creates a virtual table; there is no implicit ordering in a table,
so it follows that you should not be able to impose one in a view.

This is indeed the pure-SQL attitude, but it may be worth pointing out
that Postgres 7.1 does allow ORDER BY in subselects and views anyway.

The main reason that that seems like a good idea (IMHO at least) is that
ORDER BY together with LIMIT allow you to select specific rows in ways
that are difficult to accomplish otherwise.

regards, tom lane

Tom Lane wrote:

"Oliver Elphick" <olly@lfix.co.uk> writes:

A view creates a virtual table; there is no implicit ordering in a table,
so it follows that you should not be able to impose one in a view.

This is indeed the pure-SQL attitude, but it may be worth pointing out
that Postgres 7.1 does allow ORDER BY in subselects and views anyway.

I see I was out of date. Sorry!

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Be strong, and let your heart take courage, all you
who hope in the Lord." Psalm 31:24