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.
