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