How to keep format of views source code as entered?

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 maintain my 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, rather than the literal text you entered. Generally, you maintain your view definition separately in a source code control system 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

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

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

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, issue management 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

On Thu, Jan 7, 2021 at 4:38 PM raf <raf@raf.org> wrote:

Hi, I've only used stored functions (not views or
triggers),

Extrapolating to these other types of objects based upon experiences with
functions isn't all that helpful.

and Postgres has never altered

the code that it stores,

Right, you use functions...

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.

Now you've introduced PostgreSQL version dependency into the mix.

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

Yes, consider the original text as being official, not what is stored in
the database. Don't allow changes to get pushed to the database unless
driven from the source 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.

It cannot.

You'll also want to make sure that they all have write
access to the same git repository where the views are.

Huh?

In short, one creates a function by writing:

CREATE FUNCTION ... $$ function body written as a text literal here $$ ...;

and a view:

CREATE VIEW AS SELECT ... (rest of a select statement here) ...;

The fact that a function is simply a body of text is why it is preserved -
and generally does't get validated at the time the CREATE statement is
executed, only when it is run. CREATE VIEW takes in a fully functioning
select command, parses it, figures out its dependencies, and stores the
components and meta-data. You get all this extra benefit at the cost of
not retaining the original text.

Admittedly, the system probably should be made to save the text, should
someone wish to write such a patch. Given the generally better-accepted
version control and migration management method of maintaining one's
database structure the need and desire to add such a capability to the core
server is quite low.

David J.

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.

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

På fredag 08. januar 2021 kl. 09:38:29, skrev Markhof, Ingolf <
ingolf.markhof@de.verizon.com <mailto:ingolf.markhof@de.verizon.com>>:

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.

It isn't rocket-science, of couse, but I'm pretty sure it is implemented like
this on purpose. PG doesn't store queries you feed it either, nor any other
command. It stores the resulting structure. SQL-scripts, containing DDL/DML
should be versioned using scm like Git, not rely on the DB to store it.

--
Andreas Joseph Krogh

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 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

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 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 run a CREATE OR REPLACE VIEW.

I think you are approaching this backwards. The SQL in the repository should be the definitive version. If you wish to change the view, you change the CREATE OR REPLACE VIEW command that you have stored in the repository, and then apply that to 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 very quickly.

--
-- Christophe Pettus
xof@thebuild.com

On Sat, Jan 9, 2021 at 9:22 AM 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.

This highlights two major differences between Oracle and Postgres.
Postgres will never allow you to make an invalid view. Also, the
search_path in Postgres acts quite differently from the user context in
Oracle. There is no guarantee that a user has a schema in postgres or that
the schema would be in the search_path.

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.

If you want to use source control (and I think it's a good idea), look into
something like flywaydb or liquibase or any of the many other db schema
control frameworks.

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 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.

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