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

[Paul Förster]

Hi Ingolf,

> On 26. Jan, 2021, at 14:41, Markhof, Ingolf  
> wrote:
> 
> 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...

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 
clearly show dependencies and reading source code can be hard, especially if 
the author was one of those genius generators which produce tons of code.

Cheers,
Paul

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

[Markhof, Ingolf]

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.foers...@gmail.com] 
Sent: 14 January 2021 07:16
To: Cybertec Schönig & Schönig GmbH 
Cc: Adam Brusselback ; David G. Johnston 
; raf ; 
pgsql-generallists.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  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 
that you 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: Detlef Eppig - Vorsitzender des 
Aufsichtsrats: Francesco de Maio

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

[Paul Förster]

Hi Laurenz,

> On 14. Jan, 2021, at 04:59, Laurenz Albe  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 
that you 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

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

[David G. Johnston]

On Wed, Jan 13, 2021 at 6:40 PM Adam Brusselback 
wrote:

>  It has been a major annoyance for views with complex subqueries or where
> clauses, the PG representation is absolutely unreadable.
>

The path to a solution here is to write a schema-to-text system that
presents the derived output in a more human-friendly way instead of a
machine-readable way.  Or maybe write a formatter that takes the supposedly
unreadable output and does stuff like "change 'character varying' to
'text'".

In any case, though, what you can do is install the view in source into a
database, dump both, compare both (same version of PG), and decide whether
the database version is different from the source control version and, if
so, decide how to update the database.  It's a view, it's not like you get
to do "alter" incremental changes anyway.

Or hold your nose and adopt a path of least resistance - accept what is
easy to accomplish and be glad you aren't writing more user-friendly stuff,
but that is only cosmetically different, yourself.  Learning what the
canonical outputs mean is annoying but not hard, especially if you do have
an original human-readable document to answer questions.

David J.

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

[Laurenz Albe]

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

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

[Adam Brusselback]

> Admittedly, the system probably should be made to save the text, should
someone wish to write such a patch.

Just wanted to throw $0.02 behind this idea if anyone does want to take it
up later. Using a source control system is better obviously. But even if
you use source control it is still incredibly annoying you cannot compare
the view you have in source control to the view definition in PG and tell
if it's the same or changed. It has been a major annoyance for views with
complex subqueries or where clauses, the PG representation is absolutely
unreadable.

-Adam

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

[Alex Williams]

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


Sent with ProtonMail Secure Email.

‐‐‐ Original Message ‐‐‐
On Tuesday, January 12, 2021 4:17 PM, Alban Hertroys  wrote:

> > On 12 Jan 2021, at 20:54, Alex Williams valencesh...@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.mark...@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 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
>
> Alban Hertroys
>
> -
>
> There is always an exception to always.

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

[Alban Hertroys]

> On 12 Jan 2021, at 20:54, Alex Williams  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 
>  wrote:
> 
>> 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
>> 
> 

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

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

[Alex Williams]

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

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐ Original Message ‐‐‐
On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf 
 wrote:

> 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:
>
> createorreplaceview 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:
>
> CREATEORREPLACEVIEW myview
>
> ASSELECT 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

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

[raf]

On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston" 
 wrote:

> On Saturday, January 9, 2021, raf  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

#!/usr/bin/env python

# pg_extract_views.py - Sample script to extract views from postgres and
# construct equivalent "create or replace view" statements (doesn't handle
# temporary or recursive views).
#
# 20210110 raf 

import pgdb # pip install PyGreSQL

# You MUST change these and the password MUST be in ~/.pgpass (mode 600)
DBHOST = 'XXX_localhost'
DBNAME = 'XXX_database_name'
DBUSER = 'XXX_user_name'

# You MUST change this to match your view naming convention
VIEW_NAME_RE = '^XXX_myview_.*$'

def print_views(db):
	'''Load all of the views from the database.'''
	cursor = db.cursor()
	views = {}
	for view_name, view_options, view_sourcecode in select_views(cursor):
		print('create or replace view %s' % view_name)
		print('(')
		columns = select_columns(cursor, view_name)
		for i in range(len(columns)):
			column_name, column_type = columns[i]
			print('%s%s' % (column_name, ',' if i < len(columns) - 1 else ''))
		print(')')
		if view_options is not None:
			options = []
			if 'check_option=local' in view_options:
options.append('check_option = "local"')
			if 'check_option=cascaded' in view_options:
options.append('check_option = "cascaded"')
			if 'security_barrier=true' in view_options:
options.append('security_barrier = true')
			if 'security_barrier=false' in view_options:
options.append('security_barrier = false')
			print('with')
			print('(')
			for i in range(len(options)):
print('%s%s' % (options[i], ',' if i < len(options) - 1 else ''))
			print(')')
		print('as')
		print(view_sourcecode)
		print('')

def select_views(cursor):
	'''Given a cursor object, return a list of view names.'''
	sql = '''
		select
			c.relname as "view_name",
			c.reloptions as "view_options",
			v.definition as "view_sourcecode"
		from
			pg_class c,
			pg_views v
		where
			c.relkind = 'v' and
			c.relname ~ %(view_name_re)s and
			v.viewname = c.relname
		order by
			c.relname
	'''
	cursor.execute(sql, dict(view_name_re=VIEW_NAME_RE))
	return cursor.fetchall()

def select_columns(cursor, view_name):
	'''Given a cursor and view name, return the column names and types.'''
	sql = '''
		select
			a.attname as "column_name",
			format_type(a.atttypid, a.atttypmod) as "column_type"
		from
			pg_class c,
			pg_attribute a
		where
			c.relkind = 'v' and
			c.relname = %(name)s and
			

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

[David G. Johnston]

On Saturday, January 9, 2021, raf  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.

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

[raf]

On Sat, Jan 09, 2021 at 02:22:25PM +, "Markhof, Ingolf" 
 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 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

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

[Tim Cross]

Markhof, Ingolf  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 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 

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

[Jeremy Smith]

On Sat, Jan 9, 2021 at 9:22 AM Markhof, Ingolf <
ingolf.mark...@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 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.




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

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

[Christophe Pettus]

> On Jan 9, 2021, at 06:22, Markhof, Ingolf  
> 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
   x...@thebuild.com

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

[Markhof, Ingolf]

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

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

[Tim Cross]

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

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

[raf]

On Fri, Jan 08, 2021 at 08:38:29AM +, "Markhof, Ingolf" 
 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

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

[Adrian Klaver]

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.kla...@aklaver.com

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

[Tom Lane]

"Markhof, Ingolf"  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

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

[Tim . Colles]

On Fri, 8 Jan 2021, Karsten Hilbert wrote:


Am Fri, Jan 08, 2021 at 08:38:29AM + 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  associated with the  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.

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

[Karsten Hilbert]

Am Fri, Jan 08, 2021 at 08:38:29AM + 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

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

[Andreas Joseph Krogh]

På fredag 08. januar 2021 kl. 09:38:29, skrev Markhof, Ingolf <
ingolf.mark...@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 

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

[Markhof, Ingolf]

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

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

[David G. Johnston]

On Thu, Jan 7, 2021 at 4:38 PM raf  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.

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

[raf]

On Thu, Jan 07, 2021 at 05:33:48PM +, "Markhof, Ingolf" 
 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.mark...@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

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

[Christophe Pettus]

Hello,

> On Jan 7, 2021, at 09:33, Markhof, Ingolf  
> 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
   x...@thebuild.com

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

[Markhof, Ingolf]

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

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

[Tom Lane]

"Markhof, Ingolf"  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

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

[Christophe Pettus]

> On Jan 7, 2021, at 08:19, Markhof, Ingolf  
> 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
   x...@thebuild.com