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

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