Postgres Pro
Downloads
Home   >   mailing lists

Thread: Saving view turns SELECT * into field list

Saving view turns SELECT * into field list

From
Ben Uphoff
Date:

Hey team – I’m sure this has come up, but my search engine skills couldn’t find an explanation:

 

Why, when I save a simple view like:

 

SELECT * FROM a_table

 

…does PostgreSQL turn the * into a field list like:

 

SELECT field1, field2, field3, field4 FROM a_table

 

?

 

This is super-frustrating, as it means I’ll have to manually change an aggregating “parent” view’s select lists every time I change the “child” views.

 

Thanks for any info. -Ben

******************* PLEASE NOTE ******************* This E-Mail/telefax message and any documents accompanying this transmission may contain information that is privileged, confidential, and/or exempt from disclosure under applicable law and is intended solely for the addressee(s) named above. If you are not the intended addressee/recipient, you are hereby notified that any use of, disclosure, copying, distribution, or reliance on the contents of this E-Mail/telefax information is strictly prohibited and may result in legal action against you. Please reply to the sender advising of the error in transmission and immediately delete/destroy the message and any accompanying documents. Thank you.

Re: Saving view turns SELECT * into field list

From
Tom Lane
Date:
Ben Uphoff <buphoff@villagemd.com> writes:
> Why, when I save a simple view like:
> SELECT * FROM a_table
> …does PostgreSQL turn the * into a field list like:
> SELECT field1, field2, field3, field4 FROM a_table
> ?

Because the SQL standard says we should.  There's explicit text in there
to the effect that adding columns to the underlying table does not add
columns to the view.  Which, I'm sure, is exactly what you wished would
happen ... but they say not to.

            regards, tom lane

Re: Saving view turns SELECT * into field list

From
"David G. Johnston"
Date:
On Monday, October 15, 2018, Ben Uphoff <buphoff@villagemd.com> wrote:


Why, when I save a simple view like:

 

SELECT * FROM a_table

 

…does PostgreSQL turn the * into a field list like:

 

SELECT field1, field2, field3, field4 FROM a_table



Yes, deeply nesting views is a maintenance concern.  It works this way so the view remains stable (there may be others, the reasoning no longer really matters...).  If you really want dynamic SQL you will need to use a function.

David J.

Re: Saving view turns SELECT * into field list

From
Ben Madin
Date:
Actually, it's super useful, because if someone adds a salaries column to your staff table, it doesn't automatically appear on the front page of your corporate website... :) 

Made up example, but if you presume that data security is an important part of data management, it is a livesaver.

To get around it all you have to do is script a drop and replace action.

A last word - if you have nested views, remember that they are essentially just query aliases that return an unindexed result set... 

cheers

Ben



On 16 October 2018 at 03:50, Ben Uphoff <buphoff@villagemd.com> wrote:

Hey team – I’m sure this has come up, but my search engine skills couldn’t find an explanation:

 

Why, when I save a simple view like:

 

SELECT * FROM a_table

 

…does PostgreSQL turn the * into a field list like:

 

SELECT field1, field2, field3, field4 FROM a_table

 

?

 

This is super-frustrating, as it means I’ll have to manually change an aggregating “parent” view’s select lists every time I change the “child” views.

 

Thanks for any info. -Ben

******************* PLEASE NOTE ******************* This E-Mail/telefax message and any documents accompanying this transmission may contain information that is privileged, confidential, and/or exempt from disclosure under applicable law and is intended solely for the addressee(s) named above. If you are not the intended addressee/recipient, you are hereby notified that any use of, disclosure, copying, distribution, or reliance on the contents of this E-Mail/telefax information is strictly prohibited and may result in legal action against you. Please reply to the sender advising of the error in transmission and immediately delete/destroy the message and any accompanying documents. Thank you.



--

Ausvet Logo

Dr Ben Madin
 
BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Skype: benmadin
Address: 
5 Shuffrey Street
Fremantle, WA 6160
Australia