agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedOrder in CREATE VIEW
5+ messages / 4 participants
[nested] [flat]
* Order in CREATE VIEW
@ 2001-04-11 08:25 Marcin Wasilewski <marcingrupy@poczta.onet.pl>
2001-04-14 06:23 ` Re: Order in CREATE VIEW Oliver Elphick <olly@lfix.co.uk>
2001-04-14 07:08 ` Re: Order in CREATE VIEW will trillich <will@serensoft.com>
0 siblings, 2 replies; 5+ messages in thread
From: Marcin Wasilewski @ 2001-04-11 08:25 UTC (permalink / raw)
To: pgsql-general
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??
Best regards
Marcin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order in CREATE VIEW
2001-04-11 08:25 Order in CREATE VIEW Marcin Wasilewski <marcingrupy@poczta.onet.pl>
@ 2001-04-14 06:23 ` Oliver Elphick <olly@lfix.co.uk>
2001-04-14 08:08 ` Re: Order in CREATE VIEW Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Oliver Elphick @ 2001-04-14 06:23 UTC (permalink / raw)
To: Marcin Wasilewski <marcingrupy@poczta.onet.pl>; +Cc: pgsql-general
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order in CREATE VIEW
2001-04-11 08:25 Order in CREATE VIEW Marcin Wasilewski <marcingrupy@poczta.onet.pl>
2001-04-14 06:23 ` Re: Order in CREATE VIEW Oliver Elphick <olly@lfix.co.uk>
@ 2001-04-14 08:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-14 08:40 ` Re: Order in CREATE VIEW Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2001-04-14 08:08 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Marcin Wasilewski <marcingrupy@poczta.onet.pl>; pgsql-general
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order in CREATE VIEW
2001-04-11 08:25 Order in CREATE VIEW Marcin Wasilewski <marcingrupy@poczta.onet.pl>
2001-04-14 06:23 ` Re: Order in CREATE VIEW Oliver Elphick <olly@lfix.co.uk>
2001-04-14 08:08 ` Re: Order in CREATE VIEW Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-14 08:40 ` Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Oliver Elphick @ 2001-04-14 08:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Order in CREATE VIEW
2001-04-11 08:25 Order in CREATE VIEW Marcin Wasilewski <marcingrupy@poczta.onet.pl>
@ 2001-04-14 07:08 ` will trillich <will@serensoft.com>
1 sibling, 0 replies; 5+ messages in thread
From: will trillich @ 2001-04-14 07:08 UTC (permalink / raw)
To: pgsql-general
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-04-14 08:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-11 08:25 Order in CREATE VIEW Marcin Wasilewski <marcingrupy@poczta.onet.pl>
2001-04-14 06:23 ` Oliver Elphick <olly@lfix.co.uk>
2001-04-14 08:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-14 08:40 ` Oliver Elphick <olly@lfix.co.uk>
2001-04-14 07:08 ` will trillich <will@serensoft.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox