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

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