installation on vista

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

"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

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

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�

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

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

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

-----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.

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