Re: How to keep format of views source code as entered?

On Fri, 8 Jan 2021, Karsten Hilbert wrote:

> Am Fri, Jan 08, 2021 at 08:38:29AM +0000 schrieb Markhof, Ingolf:
>
>> I am really surprised that PostgreSQL is unable to keep the
>> source text of a view. Honestly, for me the looks like an
>> implementation gap. Consider software development. You are
>> writing code in C++ maybe on a UNIX host. And whenever you
>> feed you source code into the compiler, it will delete it,
>> keeping the resulting executable, only.
>
> You expect the compiler to keep your source code for you ?
>
> Most certainly, PostgreSQL does not delete your view source
> code, just as the compiler does.
>
> I am not so sure that analogy holds up.
>

The SQL-92 standard requires the source text of a view to be held in
order to provide the "information_schema" "view definition" column which
"contains a representation of the view descriptors” - to me though it is
open to interpretation what that actually means. MariaDB, like
PostgreSQL, does not not store an exact copy of the view source either.

The SQL-92 standard is completely explicit about column expansion: “NOTE
13: Any implicit column references that were contained in the <query
expression> associated with the <view definition> are replaced by
explicit column references in VIEW_DEFINITION.” - so any view definition
that is stored, solely for the purposes of standard compliance, will at
a minimum have to differ from the original source if the source had any
implicit column references (and by association table references as well
I assume).

Arguably if PostgreSQL held an exact copy of the view definition (except
for alterations under Note 13 above) then it should also store exact
copies of other pre-parsed objects, such as DEFAULT on table columns and
WITH on trigger clauses, in order to be useful under the OP's context.

See also:

http://www.postgresql-archive.org/idea-storing-view-source-in-system-catalogs-td1987401.html
http://www.postgresql-archive.org/Preserving-the-source-code-of-views-td5775163.html
--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.