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

Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio

> On Jan 7, 2021, at 08:19, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
> I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to
maintainmy views SQL code outside of PostgreSQL views? 

The text that you get back from the PostgreSQL system catalogs is based on the parsed version of the view definition,
ratherthan the literal text you entered.  Generally, you maintain your view definition separately in a source code
controlsystem in its original form. 

--
-- Christophe Pettus
   xof@thebuild.com

"Markhof, Ingolf" <ingolf.markhof@de.verizon.com> writes:
> I want the SLQ code of my views stored as I entered it. Is there any way to achieve this?

No.  Lots of people prefer to keep their SQL code in some sort of
source-code-control system, anyway.

            regards, tom lane

Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio

Hello,

> On Jan 7, 2021, at 09:33, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
>
> So, it looks like PostgreSQL does support saving the original source code of a view.

To be clear, PostgreSQL itself does not.  The suggestion is to use an external source code repository, such as GitHub,
GitLab,or one of (many!) other tools or products to store the view definition. 

This has benefits besides just retaining the original source code, as you mention below: Version control, tracking,
issuemanagement and commit merging, etc. 
--
-- Christophe Pettus
   xof@thebuild.com

On Thu, Jan 07, 2021 at 05:33:48PM +0000, "Markhof, Ingolf" <ingolf.markhof@de.verizon.com> wrote:

> So, it looks like PostgreSQL does support saving the original source code of a view.
> 
> What's best practise to use as a code repository?
> 
> I would expect support of multi-user access, access-right management and perhaps versioning as well…?
> 
> Thanks for your help!
> 
> Ingolf

Hi, I've only used stored functions (not views or
triggers), and I have tools for auditing, loading, and
dropping stored functions to match the code that is in
git (so updates can be easily applied to multiple
copies of the database), and Postgres has never altered
the code that it stores, otherwise, auditing the code
in the database against the code in git wouldn't work.

But since postgres does store a possibly altered parsed
version, you could alter your source to match
Postgres's parsed version of it. Maybe I encountered
this too long ago to remember having to adjust.

For version control, I'd recommend using git, or
whatever you are using for the rest of your code.

For multi-user access rights management, I'm not sure.
You can grant multiple users the right to create things
in the database. See the documentation on the grant
statement. e.g.:

  https://www.postgresql.org/docs/12/sql-grant.html

I'm guessing that you want:

  grant create on database ... to ...

But I don't know if it can be restricted to only
creating views. If not, it might grant too much access.
You'll also want to make sure that they all have write
access to the same git repository where the views are.

cheers,
raf

> From: Markhof, Ingolf [mailto:ingolf.markhof@de.verizon.com]
> Sent: 07 January 2021 17:19
> To: pgsql-general@lists.postgresql.org
> Subject: [E] How to keep format of views source code as entered?
> 
> Hi!
> 
> Switching from Oracle SLQ to PostgreSQL I am facing the issue that
> the SQL code the system returns when I open a views source code is
> different from the code I entered. The code is formatted differently,
> comments are gone and e.g. all text constants got an explicit cast to
> ::text added. (see sample below).
> 
> I want the SLQ code of my views stored as I entered it. Is there any
> way to achieve this? Or will I be forced to maintain my views SQL code
> outside of PostgreSQL views?
> 
> Any hints welcome!
> 
> Here is an example:
> 
> I enter this code to define a simple view:
> 
> create or replace view myview as
> select
>   product_id,
>   product_acronym
> from
>   products -- my comment here
> where
>   product_acronym = 'ABC'
> ;
> 
> However, when I open the view my SQL client (DBeaver) again, this is what I get:
> 
> CREATE OR REPLACE VIEW myview
> AS SELECT product_id,
>     product_acronym
>    FROM products
>   WHERE product_acronym = 'ABC'::text;
> 
> So, the formatting changed, keywords are capitalized, the comment I
> added in the from-part has gone and the text constant 'ABC' changed to
> 'ABC'::text.
> 
> Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 -
Geschäftsführer:Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
 

Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio

Thanks for your comments and thoughts.

 

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. And you could not even store your source code on the UNIX system. Instead, you'd be forced to do so in a separate system, like GitHub. Stupid, isn't it? Right. There are good reasons to store the source code on GitHub or alike anyhow. Especially when working on larger project and when collaborating with many people. But in case of rather small project with a few people only, this might be an overkill.

 

It shouldn't be rocket science to enable PostgreSQL to store the original source code as well. It's weird PostgreSQL is not doing it.

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.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

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.

"Markhof, Ingolf" <ingolf.markhof@de.verizon.com> writes:
> 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.

Perhaps, but the "gap" is wider than you seem to think.  Consider

CREATE TABLE t1 (f1 int, f2 text);
CREATE VIEW v1 AS SELECT f2 FROM t1;
ALTER TABLE t1 RENAME COLUMN f2 TO zed;
\d+ v1
                            View "public.v1"
 Column | Type | Collation | Nullable | Default | Storage  | Description 
--------+------+-----------+----------+---------+----------+-------------
 f2     | text |           |          |         | extended | 
View definition:
 SELECT t1.zed AS f2
   FROM t1;

At this point the original text of the view is useless; with
another rename or two it could become downright misleading.

Another issue revolves around the fact that a textual SQL statement
is seldom totally unambiguous.  In the above example, the fact that
"t1" refers to public.t1 and not some other t1 depends on the
search_path as it stood at CREATE VIEW time.  If you change your
search_path you might need an explicit schema qualification.
The reverse-parsed view display accounts for that:

# set search_path = pg_catalog;
# \d+ public.v1
                            View "public.v1"
 Column | Type | Collation | Nullable | Default | Storage  | Description 
--------+------+-----------+----------+---------+----------+-------------
 f2     | text |           |          |         | extended | 
View definition:
 SELECT t1.zed AS f2
   FROM public.t1;

but a static source text could not.  In PG this hazard applies to
functions and operators not only tables.  If pg_dump regurgitated
the original view text, there would be trivially-exploitable
security holes that allow some other user to take control of your
view after a dump/reload.

We actually used to store both text and parsed versions of some
sub-constructs, such as CHECK constraints and column default values.
We got rid of the text versions because there was no reasonable way
to keep them up-to-date.  (And, AFAIR, there hasn't been a lot of
push-back about those catalog columns disappearing.)  So I don't
think we'd accept a patch to store the text form of a view, unless
some solution to these issues were provided.

            regards, tom lane

On 1/8/21 12:38 AM, Markhof, Ingolf wrote:
> Thanks for your comments and thoughts.
> 
> 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. And you could 
> not even store your source code on the UNIX system. Instead, you'd be 
> forced to do so in a separate system, like GitHub. Stupid, isn't it? 
> Right. There are good reasons to store the source code on GitHub or 
> alike anyhow. Especially when working on larger project and when 
> collaborating with many people. But in case of rather small project with 
> a few people only, this might be an overkill.

The projects I work on are my own personal ones and I find an 
independent version control solution the way to go for the following 
reasons:

1) It is easy.
    a) mkdir project_src
    b) cd project_src
    c) git init
Now you are set.

2) Even my simple projects generally have multiple layers.
    a) Database
    b) Middleware
    c) UI
And also multiple languages. It makes sense to me to keep all
that information in one repo then having each layer operate independently.

3) It allows me to work on test and production code without stepping on 
each other.

4) It serves as an aid to memory. Answers the question; What was I 
thinking when I did that? More important it helps anyone else that might 
have to deal with the code.

FYI, the program I use to manage database changes is 
Sqitch(https://sqitch.org/).

> 
> It shouldn't be rocket science to enable PostgreSQL to store the 
> original source code as well. It's weird PostgreSQL is not doing it.
> 
> Regards,
> 
> Ingolf
> 
> 
> *Verizon Deutschland GmbH* - Sebrathweg 20, 44149 Dortmund, Germany - 
> Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - 
> Vorsitzender des Aufsichtsrats: Francesco de Maio
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, Jan 08, 2021 at 08:38:29AM +0000, "Markhof, Ingolf" <ingolf.markhof@de.verizon.com> wrote:

> Thanks for your comments and thoughts.
> [...]
> And you could not even store your source code on the UNIX
> system. Instead, you'd be forced to do so in a separate system, like
> GitHub. Stupid, isn't it? Right.
> [...] 
> Regards,
> Ingolf

I don't think your conclusions are correct. There is
nothing that can stop you from "storing your source
code on the UNIX system". You don't have to use github.
But even if you do you github, you would first need to
store your source code on a file system, so that a
local git repository could see it, and push it to
github. You don't even have to use git if you really
don't want to.

cheers,
raf

Adrian Klaver <adrian.klaver@aklaver.com> writes:

> On 1/8/21 12:38 AM, Markhof, Ingolf wrote:
>> Thanks for your comments and thoughts.
>>
>> 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. And you could
>> not even store your source code on the UNIX system. Instead, you'd be
>> forced to do so in a separate system, like GitHub. Stupid, isn't it?
>> Right. There are good reasons to store the source code on GitHub or
>> alike anyhow. Especially when working on larger project and when
>> collaborating with many people. But in case of rather small project with
>> a few people only, this might be an overkill.
>
> The projects I work on are my own personal ones and I find an
> independent version control solution the way to go for the following
> reasons:
>
> 1) It is easy.
>   a) mkdir project_src
>   b) cd project_src
>   c) git init
> Now you are set.
>
> 2) Even my simple projects generally have multiple layers.
>   a) Database
>   b) Middleware
>   c) UI
> And also multiple languages. It makes sense to me to keep all
> that information in one repo then having each layer operate independently.
>
> 3) It allows me to work on test and production code without stepping on
> each other.
>
> 4) It serves as an aid to memory. Answers the question; What was I
> thinking when I did that? More important it helps anyone else that might
> have to deal with the code.
>
> FYI, the program I use to manage database changes is
> Sqitch(https://sqitch.org/).
>
>>

This is essentially my workflow as well. I have even used sqitch too.

While this has worked well for my projects, attempts to introduce the
discipline necessary to use such a workflow in a team has largely
failed. This seems to be due to 2 main reasons -

1. Lack of SCCM support built into common tools. There are very few
tools which have version control support built in (I believe the jet
brains product does). In particular, pgAdmin would benefit here (maybe
pgadmin4 does, I've not tried it in a few years).

2. Poor SCCM and DBMS Understanding. Despite it being 2021 and both
version control and databases being two very common technologies you
need to interact with as a developer, I'm still surprised at how poorly
many developers understand these tools. I still frequently come across
really bad workflows and practices with version control and code which
uses the database as little more than a bit bucket, which re-implement
searching and sorting at the client level (and then often moan about
poor performance issues).

My editor has good support for psql and psql has always been my goto
tool for PG. As my editor also has good git support, my workflow works
well. However, most people I've worked with prefer things like pgadmin.
Tom Lane responded in this thread to point out some of the complexities
which make it difficult to maintain current code source within the
database itself. This is definitely something which should be kept in
version control. The problem is, if your tool does not support the
version control system, it is too easy to forget/bypass that stage. When
you use something like pgadmin, it is far too easy to modify the source
definitions in the database without ever updating the sources on disk in
the version control working directory and the changes get lost.

The other big challenge is dependency management. Keeping track of what
is affected by a change to a table definition can be a challenge within
a complex system. I've yet to find a good solution to that issue. It is
probably something which needs to be built into a tool. In the past,
I've used a modified sqitch approach that also maintains a small 'dbadm'
schema containing metadata to track dependencies. Although this worked
OK, especially if you understood how all the bits fit together, it still
had many corner cases and to some extent highlighted the complexities involved.

--
Tim Cross

Tom, all,

when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table
usingthe old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting
inmy context, i.e. my schema. So it is perfectly clear what table I am referring to. 

Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an
dataanalyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new
requirements.Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required.  

What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I finally
needto copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver editor
windowand copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced
collaborationfeatures which I don't really need. At the price that code transfer from SQL (DBeaver) to the code
repositoryand vice versa is complete manually?! This doesn't really look like an enhancement. 

Most likely, there are more professional ways to do that. I'd be glad to get advice.

What I would like to have is something that would automatically update the SQL code in the software repository when I
runa CREATE OR REPLACE VIEW. 

Ingolf



Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer:
DetlefEppig - Vorsitzender des Aufsichtsrats: Francesco de Maio 

> On Jan 9, 2021, at 06:22, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
> What I would like to have is something that would automatically update the SQL code in the software repository when I
runa CREATE OR REPLACE VIEW. 

I think you are approaching this backwards.  The SQL in the repository should be the definitive version.  If you wish
tochange the view, you change the CREATE OR REPLACE VIEW command that you have stored in the repository, and then apply
thatto the database so it now has the new view definition. 

You may not, in a small independent project, feel the need for a source code repository, but it becomes very useful
veryquickly. 

--
-- Christophe Pettus
   xof@thebuild.com

Markhof, Ingolf <ingolf.markhof@de.verizon.com> writes:

> Tom, all,
>
> when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table
usingthe old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting
inmy context, i.e. my schema. So it is perfectly clear what table I am referring to.
 
>
> Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an
dataanalyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new
requirements.Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required.
 
>
> What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I
finallyneed to copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver
editorwindow and copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced
collaborationfeatures which I don't really need. At the price that code transfer from SQL (DBeaver) to the code
repositoryand vice versa is complete manually?! This doesn't really look like an enhancement.
 
>
> Most likely, there are more professional ways to do that. I'd be glad to get advice.
>
> What I would like to have is something that would automatically update the SQL code in the software repository when I
runa CREATE OR REPLACE VIEW.
 
>

Even with Oracle, I found it works much better to keep all your DDL/DML
in files stored within the OS file system. This approach also works fine
with tools like DBeaver, pgAdmin, etc as all of these files also support
working with files. The main benefits I find with this approach are -

1. Adding version control is easy. Doesn't matter if it is git,
hg, bzr, svn or rcs - any version control system works fine. It doesn't
have to be a cloud service like github, though some sort of centralised
repository can be useful for managing things like backups and sharing
code across a team (I've used gitlab hosted locally t great success).
Most editors also have built-in support for common version control
systems, so the additional overhead associated with using a version
control system is very little.

2. Having all your code in version control makes tracking changes
trivial. This is often really useful in tracking down problems/bugs
caused by a change and other diagnostics. More than once, I have found
I've gone down a bad path of changes and want to restore a previous
version. Assuming you use your version control system appropriately,
this becomes trivial. If your code is only in the db, once you make
changes, the old code is gone and cannot easily be restored.

3. Having all the DDL/DML in files makes data migration very simple. I
will typically have a development environment where I develop my DDL/DM
which is separate from the production environment. This can be very
important even in data analysis and data mining type applications as it
allows you to develop complex and possibly resource hungry DML in an
environment where mistakes won't impact production systems. It also
means you can have a dev environment which is populated with specific
data sets which have been defined to help in the development process
e.g. perhaps smaller, so tests run faster or perhaps ensuring all
possible data permutations are included etc. If all your DDL/DML are in
files, seting up a new environment is as simple as writing a basic
script and using psql (pg) or sqlplus (oracle) to load the DDL/DML. In
simpler environments, you can even use a naming scheme for the files
which sets the order - loading the data then becomes as easy as 'psql
*', avoiding the need to write scripts (even though writing the scripts
is typically trivial).

4. Having all your DDL/DML in files allows you to use the many powerful
text manipulation tools which exist on most platforms. While such tools
are not often required, when they are, it can be a real bonus. Being
able to use tools like sed, awk, perl, etc have saved my bacon more than
once. Over the years, I have also built up a very useful library of
techniques, templates etc. Being able to quickly and easily access this
library is very useful.

5. While the built in editors in tools like DBeaver and pgAdmin are OK,
I find they are rarely as good as my preferred editor and I often get
frustrated at having to know/learn the editors of different tools.
Having everything based on files means I can use my preferred editor,
which has support for things like completion, familiar syntax
highlighting and key bindings, templates etc. These days, many editors
even have built-in support for popular databases like pgsql and oracle,
so you can load the code and run it without having to leave your editor.

Having the code in the database can be useful. I've used this in oracle
to provide enhanced debugging support when developing stored procedures
and packages and enhanced reporting in unit testing. However, you really
don't want this to be the definitive master for your source code. Your
source code benefits from being in a location which makes backup,
restoration and migration easy/fast, where changes can be tracked, analysed and rolled
back, where re-factoring can use advanced tools and can work across
projects, not just on a single script, where code sharing is easy and
where people can use their preferred tool rather than being forced to
use something which understands the database.

--
Tim Cross

On Sat, Jan 09, 2021 at 02:22:25PM +0000, "Markhof, Ingolf" <ingolf.markhof@de.verizon.com> wrote:

> Tom, all,
> 
> when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL
> client) marks views that refer to the table using the old column name
> as erroneous. So, I can easily identify these cases. And of course
> I, as a user, I am acting in my context, i.e. my schema. So it is
> perfectly clear what table I am referring to.
> 
> Please note: I am not developing any PL/SQL code. I don't have big
> development projects. I have more the role of an data analyst. I
> just create rather complex SQL queries which, from time to time, may
> need to be adopted to some new requirements. Or peers want to (re-)
> use (part of) my SQL queries. There is not really much versioning
> required.
> 
> What I understood so far is: I can use e.g. DBeaver to interact with
> PostgreSQL, to develop my SQL code. But I finally need to copy the SQL
> code into e.g. Github. Which is a manual process. I'd mark the SQL
> code in the DBeaver editor window and copy&paste it into some file in
> e.g. GitHub. Using Github, I'd get version control and other enhanced
> collaboration features which I don't really need. At the price that
> code transfer from SQL (DBeaver) to the code repository and vice versa
> is complete manually?! This doesn't really look like an enhancement.
> 
> Most likely, there are more professional ways to do that. I'd be glad
> to get advice.
> 
> What I would like to have is something that would automatically update
> the SQL code in the software repository when I run a CREATE OR REPLACE
> VIEW.
> 
> Ingolf

Hi,

If there is a software repository, then I would
recommend considering not using tools like DBeaver to
develop your SQL views. Instead, develop them in
relation with the repository tools, and use psql or
similar to load the view into the database(s).

Alternatively, if you do modify the views "live" in the
databse, get (or have someone create) a tool to fetch
the code of the view from the database, and write it to
a file that can be committed into the repository.

It doesn't have to be copy and paste. A simple program
can be written to extract view source code and write it
to a file. Perhaps your colleagues that want to re-use
your source code can implement it. Such a tool would be
useful with or without a repository.

Here's an example of such a query but it's for
procedures/functions, and would need to be
very different for views.

    select
        p.proname, -- name
        p.proretset, -- returns setof?
        p.proisstrict, -- strict 't' or 'f'
        p.provolatile, -- volatile or stable 'v' or 's'
        p.prosecdef, -- security definer 't' or 'f'
        p.pronargs, -- number of in arguments
        p.prorettype, -- return type
        p.proargtypes, -- space-separated list of in arg types
        p.proallargtypes, -- array of in/out arg types (iff there are out args)
        p.proargmodes, -- array of in/out arg modes like {i,o,o} (iff there are out args)
        p.proargnames, -- array of in/out arg names like {id,code,name}
        p.prosrc, -- source code
        cast(cast(p.oid as regprocedure) as text) -- nice signature
    from
        pg_user u,
        pg_proc p
    where
        u.usename = current_user and
        p.proowner = u.usesysid and
        p.proname like 'myfunc_%' -- Your naming convention
    order by
        p.proname

The above returns enough information to construct a
corresponding create function statement (except for any
knowledge of precision and scale of numeric parameters).

Actually, I just had a look at the pg_views system
catalog where the source code for views is stored, and
it doesn't seem to contain enough information to
reconstruct a create view statement. It only contains
these columns:

  schemaname
  viewname
  viewowner
  definition

But definition is just the query itself.

There is no list of column names (like there is with
procedures in pg_proc).

You can tell the difference between a temporary and
non-temporary view because the schemaname is different
for temporary views (e.g. pg_temp_3, rather than
public).

I don't know if you could tell whether a view is
recursive or not.

And it doesn't look like you can determine if a view
has a local or cascaded check_option parameter, or the
security_barrier parameter.

Is all of that information stored somewhere else in the
system catalogs?

Without them, this query would only find the names and
query code of views:

    select
        v.viewname,
        v.definition
    from
        pg_views v
    where
        v.viewname like 'myview_%'; -- Your naming convention

Is there a query that can be used to obtain all of the
information needed to reconstruct the create view
statement that corresponds to a view in pg_views?

cheers,
raf

Actually, I just had a look at the pg_views system
catalog where the source code for views is stored, and
it doesn't seem to contain enough information to
reconstruct a create view statement. It only contains
these columns:

  schemaname
  viewname
  viewowner
  definition

But definition is just the query itself.

There is no list of column names (like there is with
procedures in pg_proc).

Is all of that information stored somewhere else in the
system catalogs?

On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote:

> On Saturday, January 9, 2021, raf <raf@raf.org> wrote:
> 
> > Actually, I just had a look at the pg_views system
> > catalog where the source code for views is stored, and
> > it doesn't seem to contain enough information to
> > reconstruct a create view statement. It only contains
> > these columns:
> >
> >   schemaname
> >   viewname
> >   viewowner
> >   definition
> >
> > But definition is just the query itself.
> >
> > There is no list of column names (like there is with
> > procedures in pg_proc).
> >
> > Is all of that information stored somewhere else in the
> > system catalogs?
> >
> 
> Views are relation-like and thus are primarily recorded on pg_class.
> 
> David J.

Hi David,

Thanks. That's very helpful. The following query should
return enough information to re-construct create view
statements:

    select
        c.relname as "view_name",
        a.attname as "column_name",
        format_type(a.atttypid, a.atttypmod) as "column_type",
        v.definition as "view_sourcecode",
        c.reloptions as "view_options"
    from
        pg_class c,
        pg_attribute a,
        pg_views v
    where
        c.relkind = 'v' and
        c.relname like 'myview%' and -- Your naming convention
        a.attrelid = c.oid and
        v.viewname = c.relname
    order by
        c.relname,
        a.attnum;

Note that view_options can look like
"{check_option=local,security_barrier=false}".

Also, this won't find temporary views (I'm probably not
looking in the right place for them).

Also, it probably doesn't handle recursive views.

But for simple views, it should make a basis for
extracting views into files that can be added to a
source code repository.

I've attached a Python script that can output "create
or replace view" statements for the views in a database
that match a particular naming convention.

Note that:

  - Login details are hard-coded and must be changed.
  - View naming convention is hard-coded and must be changed.
  - It doesn't handle temporary or recursive views.
  - It does handle check_option and security_barrier options.
  - All views are output to stdout, not separate files.
  - Comments are not included (code is in post-parse state).
  - It should probably drop then create each view.

I hope it's useful to show how this could be done, but
the loss of comments would bother me. I'd want the
documentation in the source code repository. Having
access to the original source would be much nicer (and
enable auditing views in the database against the views
in the source code repository to see if they've been
changed).

cheers,
raf

Hi!

 

Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a views source code is different from the code I entered. The code is formatted differently, comments are gone and e.g. all text constants got an explicit cast to ::text added. (see sample below).

 

I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to maintain my views SQL code outside of PostgreSQL views?

 

Any hints welcome!

 

Here is an example:

 

I enter this code to define a simple view:

 

create or replace view myview as

select

  product_id,

  product_acronym

from

  products -- my comment here

where

  product_acronym = 'ABC'

;

 

However, when I open the view my SQL client (DBeaver) again, this is what I get:

 

CREATE OR REPLACE VIEW myview

AS SELECT product_id,

    product_acronym

   FROM products

  WHERE product_acronym = 'ABC'::text;

 

So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text constant 'ABC' changed to 'ABC'::text.

 

 

 

 

Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio

> On 12 Jan 2021, at 20:54, Alex Williams <valenceshell@protonmail.com> wrote:
>
> Hi Ingolf,
>
> For comments in views, I create a unused CTE and put my comments there, e.g.
>
> WITH v_comments AS (
>          SELECT 'this is my comment' AS comment
>         )
>
> Alex

You do know about COMMENT ON VIEW v_comments IS ’this is my comment’, right?


> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
> On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
>
>> Hi!
>>
>>
>>
>> Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a
viewssource code is different from the code I entered. The code is formatted differently, comments are gone and e.g.
alltext constants got an explicit cast to ::text added. (see sample below). 
>>
>>
>>
>> I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to
maintainmy views SQL code outside of PostgreSQL views? 
>>
>>
>>
>> Any hints welcome!
>>
>>
>>
>> Here is an example:
>>
>>
>>
>> I enter this code to define a simple view:
>>
>>
>>
>> create or replace view myview as
>>
>> select
>>
>>   product_id,
>>
>>   product_acronym
>>
>> from
>>
>>   products -- my comment here
>>
>> where
>>
>>   product_acronym = 'ABC'
>>
>> ;
>>
>>
>>
>> However, when I open the view my SQL client (DBeaver) again, this is what I get:
>>
>>
>>
>> CREATE OR REPLACE VIEW myview
>>
>> AS SELECT product_id,
>>
>>     product_acronym
>>
>>    FROM products
>>
>>   WHERE product_acronym = 'ABC'::text;
>>
>>
>>
>> So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text
constant'ABC' changed to 'ABC'::text. 
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 -
Geschäftsführer:Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio 
>>
>

Alban Hertroys
--
There is always an exception to always.

Ugh, I wasn't and just tried it, thanks.

I've saved comments before on pgadmin and wasn't aware it using a sql statement to save it, I thought it was local to
myenvironment. 


Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, January 12, 2021 4:17 PM, Alban Hertroys <haramrae@gmail.com> wrote:

> > On 12 Jan 2021, at 20:54, Alex Williams valenceshell@protonmail.com wrote:
> > Hi Ingolf,
> > For comments in views, I create a unused CTE and put my comments there, e.g.
> > WITH v_comments AS (
> > SELECT 'this is my comment' AS comment
> > )
> > Alex
>
> You do know about COMMENT ON VIEW v_comments IS ’this is my comment’, right?
>
> > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
> > On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf ingolf.markhof@de.verizon.com wrote:
> >
> > > Hi!
> > > Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a
viewssource code is different from the code I entered. The code is formatted differently, comments are gone and e.g.
alltext constants got an explicit cast to ::text added. (see sample below). 
> > > I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to
maintainmy views SQL code outside of PostgreSQL views? 
> > > Any hints welcome!
> > > Here is an example:
> > > I enter this code to define a simple view:
> > > create or replace view myview as
> > > select
> > > product_id,
> > > product_acronym
> > > from
> > > products -- my comment here
> > > where
> > > product_acronym = 'ABC'
> > > ;
> > > However, when I open the view my SQL client (DBeaver) again, this is what I get:
> > > CREATE OR REPLACE VIEW myview
> > > AS SELECT product_id,
> > >
> > >     product_acronym
> > >
> > >
> > > FROM products
> > > WHERE product_acronym = 'ABC'::text;
> > > So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text
constant'ABC' changed to 'ABC'::text. 
> > > Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 -
Geschäftsführer:Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio 
>
> Alban Hertroys
>
> -----------------
>
> There is always an exception to always.

On Wed, 2021-01-13 at 20:39 -0500, Adam Brusselback wrote:
> > Admittedly, the system probably should be made to save the text, should someone wish to write such a patch. 
> 
> It has been a major annoyance for views with complex subqueries or where clauses, the PG representation is absolutely
unreadable.

This is not going to happen, and I dare say that such a patch would be rejected.

Since PostgreSQL stores view definitions in their parsed form, the query does
not contain the name of the used objects, but only their object ID.

This allows you for example to rename the underlying objects, because that
does not change the object ID:

CREATE TABLE t (id integer);

CREATE VIEW v AS SELECT * FROM t;

\d+ v
[...]
View definition:
 SELECT t.id
   FROM t;

ALTER TABLE t RENAME TO quaxi;

\d+ v
[...]
View definition:
 SELECT quaxi.id
   FROM quaxi;

If PostgreSQL were to store the original text, either that text would become
wrong, or you would have to forbid renaming of anything that is referenced
by a view.

A database is no source versioning system.  The next thing someone will request
is that the original CREATE TABLE or CREATE INDEX statements should be preserved.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Hi Laurenz,

> On 14. Jan, 2021, at 04:59, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> If PostgreSQL were to store the original text, either that text would become
> wrong, or you would have to forbid renaming of anything that is referenced
> by a view.

this is why views, procedures, functions and packages can become invalid in Oracle, which I really hate because as a
DBA,it's almost impossible to quickly see (or in fact see at all) why this happens, or having to debug applications
thatyou don't know and/or can't fix anyway. Oracle's invalid object concept doesn't make sense. 

So, I'm not at all in favor of saving the original statement text.

Cheers,
Paul

Hi!

Today, I made an astonishing / disappointing experience related to that source code topic:

You may not be able to delete tables / views that are referenced by other users objects,
e.g. views. Unless you add the CASCADE option which will cause all depending views to be
deleted as well. And the CASCASE will work and delete the other users view even when you
don't have the permission to drop that other users view!

Looks like the Oracle concept of marking views as invalid makes some sense...

Regards,
Ingolf



-----Original Message-----
From: Paul Förster [mailto:paul.foerster@gmail.com]
Sent: 14 January 2021 07:16
To: Cybertec Schönig & Schönig GmbH <laurenz.albe@cybertec.at>
Cc: Adam Brusselback <adambrusselback@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; raf <raf@raf.org>;
pgsql-generallists.postgresql.org<pgsql-general@lists.postgresql.org> 
Subject: [E] Re: How to keep format of views source code as entered?

Hi Laurenz,

> On 14. Jan, 2021, at 04:59, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> If PostgreSQL were to store the original text, either that text would become
> wrong, or you would have to forbid renaming of anything that is referenced
> by a view.

this is why views, procedures, functions and packages can become invalid in Oracle, which I really hate because as a
DBA,it's almost impossible to quickly see (or in fact see at all) why this happens, or having to debug applications
thatyou don't know and/or can't fix anyway. Oracle's invalid object concept doesn't make sense. 

So, I'm not at all in favor of saving the original statement text.

Cheers,
Paul



Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer:
DetlefEppig - Vorsitzender des Aufsichtsrats: Francesco de Maio 

Hi Ingolf,

> On 26. Jan, 2021, at 14:41, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
>
> You may not be able to delete tables / views that are referenced by other users objects, e.g. views. Unless you add
theCASCADE option which will cause all depending views to be deleted as well. And the CASCASE will work and delete the
otherusers view even when you don't have the permission to drop that other users view! 
>
> Looks like the Oracle concept of marking views as invalid makes some sense...

yes, PostgreSQL's and Oracle's approaches IMHO both make perfect sense in their own way, depending on your design
philosophy.I never said otherwise. I just said that I hate to debug invalid objects in Oracle because Oracle does not
clearlyshow dependencies and reading source code can be hard, especially if the author was one of those genius
generatorswhich produce tons of code. 

Cheers,
Paul