Re: How to keep format of views source code as entered? - Mailing list pgsql-general

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

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

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

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

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

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

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

I'm guessing that you want:

  grant create on database ... to ...

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

cheers,
raf

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