Thread: installation on vista

installation on vista

From
"Watson, Nathaniel"
Date:

I have downloaded the one click installer on Vista.  I right click on the executable and select run as administrator, and an empty dialog box appears.  This is as far as I can go.  Any suggestions?

SELECT * in a view

From
"Rob Richardson"
Date:
Greetings!
 
Today's request for the impossible:
 
My database has a view that is not filtered enough for one of my reports.  The report (done in CR XI) has a dynamic parameter based on that view, but only records with a certain field (complete = 1) should be shown.
 
The easiest way to do that is to copy the existing view into a new view with a more restrictive WHERE clause.  Also, the new view is likely to be useful in various places throughout our system.
 
The old view and the new view should have the same fields.  So, I tried something like this:
 
CREATE VIEW new_view AS
    SELECT * FROM old_view
    WHERE complete = 1;
 
The query was accepted and the new_view was created. 
 
But when I went back into PGAdmin and looked at the definition of new_view, I saw something like this:
 
CREATE VIEW new_view AS
    SELECT old_view.field1, old_view.field2,  <snip> FROM old_view
   WHERE complete = 1;
 
That's not what I want.  That means that if old_view changes, new_view will not reflect the changes.  Is there any way to get new_view to automatically include all fields from old_view, no matter how many or how few fields there are?
 
I'm guessing not, just because of the fact that if I try to add a field to a view, I get an error telling me I can't change the number of columns in the view unless I drop it first.  I'm not sure of the reason behind that, but if I can't do it directly, I'm pretty durn sure I wouldn't be able to do it indirectly by using SELECT * in the view's definition.
 
RobR
 

Re: SELECT * in a view

From
Tom Lane
Date:
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> The old view and the new view should have the same fields.  So, I tried
> something like this:

> CREATE VIEW new_view AS
>     SELECT * FROM old_view
>     WHERE complete = 1;

> The query was accepted and the new_view was created.

> But when I went back into PGAdmin and looked at the definition of
> new_view, I saw something like this:

> CREATE VIEW new_view AS
>     SELECT old_view.field1, old_view.field2,  <snip> FROM old_view
>    WHERE complete = 1;

> That's not what I want.  That means that if old_view changes, new_view
> will not reflect the changes.  Is there any way to get new_view to
> automatically include all fields from old_view, no matter how many or
> how few fields there are?

No.  This behavior is specifically required by the SQL standard: the
result rowtype of a view is determined when the view is created, and
is not supposed to change when underlying tables have columns added.
That's why we expand * when the view is created.

            regards, tom lane

Re: SELECT * in a view

From
Merlin Moncure
Date:
On Wed, Apr 21, 2010 at 1:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Rob Richardson" <Rob.Richardson@rad-con.com> writes:
>> The old view and the new view should have the same fields.  So, I tried
>> something like this:
>
>> CREATE VIEW new_view AS
>>     SELECT * FROM old_view
>>     WHERE complete = 1;
>
>> The query was accepted and the new_view was created.
>
>> But when I went back into PGAdmin and looked at the definition of
>> new_view, I saw something like this:
>
>> CREATE VIEW new_view AS
>>     SELECT old_view.field1, old_view.field2,  <snip> FROM old_view
>>    WHERE complete = 1;
>
>> That's not what I want.  That means that if old_view changes, new_view
>> will not reflect the changes.  Is there any way to get new_view to
>> automatically include all fields from old_view, no matter how many or
>> how few fields there are?
>
> No.  This behavior is specifically required by the SQL standard: the
> result rowtype of a view is determined when the view is created, and
> is not supposed to change when underlying tables have columns added.
> That's why we expand * when the view is created.

You can skirt this problem w/composite types:
create view v as select 1 as a, 2 as b, 3 as c;
CREATE VIEW
create view vv as select v from v;
CREATE VIEW
select * from vv;
    v
---------
 (1,2,3)
(1 row)
create or replace view v as select 1 as a, 2 as b, 3 as c, 4 as d;
CREATE VIEW

select (v).* from vv;
 a | b | c | d
---+---+---+---
 1 | 2 | 3 | 4
(1 row)

btw tom: does the sql standard define that in the case of:
create view select (foo).* from foo;

so that this restriction could be possibly relaxed for cases involving
composite types depending if you do select * vs select (something).*?

merlin

Re: SELECT * in a view

From
Andreas Kretschmer
Date:
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> > That's not what I want.  That means that if old_view changes, new_view
> > will not reflect the changes.  Is there any way to get new_view to
> > automatically include all fields from old_view, no matter how many or
> > how few fields there are?
>
> No.  This behavior is specifically required by the SQL standard: the
> result rowtype of a view is determined when the view is created, and
> is not supposed to change when underlying tables have columns added.
> That's why we expand * when the view is created.

Right, and additional, don't use select *, in particular production
code.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: SELECT * in a view

From
Merlin Moncure
Date:
On Wed, Apr 21, 2010 at 2:14 PM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> > That's not what I want.  That means that if old_view changes, new_view
>> > will not reflect the changes.  Is there any way to get new_view to
>> > automatically include all fields from old_view, no matter how many or
>> > how few fields there are?
>>
>> No.  This behavior is specifically required by the SQL standard: the
>> result rowtype of a view is determined when the view is created, and
>> is not supposed to change when underlying tables have columns added.
>> That's why we expand * when the view is created.
>
> Right, and additional, don't use select *, in particular production
> code.

I don't agree.  This entirely depends on context.  It is completely
reasonable to want to be able expand a composite type without
explicitly listing the fields, or do other things:

select foo.* from foo join bar on ... -- what's wrong with this?
select (v).* from (select aggfunc(foo) as v from foo where ...) -- or this?

merlin

Re: installation on vista

From
Craig Ringer
Date:
On 22/04/2010 1:05 AM, Watson, Nathaniel wrote:
> I have downloaded the one click installer on Vista.

 From where? What version?

> I right click on
> the executable and select run as administrator, and an empty dialog box
> appears. This is as far as I can go. Any suggestions?

Does this dialog appear *after* the  UAC run-as-admin prompt? Or before?

--
Craig Ringer

Re: installation on vista

From
Dave Page
Date:
On Thu, Apr 22, 2010 at 1:47 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 22/04/2010 1:05 AM, Watson, Nathaniel wrote:
>>
>> I have downloaded the one click installer on Vista.
>
> From where? What version?
>
>> I right click on
>> the executable and select run as administrator, and an empty dialog box
>> appears. This is as far as I can go. Any suggestions?
>
> Does this dialog appear *after* the  UAC run-as-admin prompt? Or before?

Also, is a log created in %TEMP%?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

Re: installation on vista

From
"Watson, Nathaniel"
Date:



-----Original Message-----
From: Dave Page [mailto:dpage@pgadmin.org]
Sent: Thu 4/22/2010 3:35 AM
To: Craig Ringer
Cc: Watson, Nathaniel; pgsql-general@postgresql.org
Subject: Re: [GENERAL] installation on vista

On Thu, Apr 22, 2010 at 1:47 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 22/04/2010 1:05 AM, Watson, Nathaniel wrote:
>>
>> I have downloaded the one click installer on Vista.
>
> From where? What version?
>
>> I right click on
>> the executable and select run as administrator, and an empty dialog box
>> appears. This is as far as I can go. Any suggestions?
>
> Does this dialog appear *after* the  UAC run-as-admin prompt? Or before?

Also, is a log created in %TEMP%?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

> I downloaded postgresql-8.4.3-1-windows.exe from
> http://www.enterprisedb.com/products/pgdownload.do#windows
> on an HP Pavilion dv5 Notebook PC.
> This occurs After I select "continue" in the UAC prompt.

It appears that no log is being created in %TEMP% that as a result of this problem.

Re: installation on vista

From
Dave Page
Date:
On Fri, Apr 23, 2010 at 4:03 PM, Watson, Nathaniel <nwatso16@uncc.edu> wrote:
>
> It appears that no log is being created in %TEMP% that as a result of this
> problem.

Very odd. Does anything get created in %TEMP%? That early in the
installation it's probably unpacking some of the files it'll need for
the pre-flight checks, such as the VC++ runtimes. They should be
fairly easy to spot - a directory called postgresql_installer for
example.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company