Saving view turns SELECT * into field list

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

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.

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.

--

[image: Ausvet Logo] <https://www.ausvet.com.au/&gt;

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
ben@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia