View definition formatting

Dave Page wrote:

Hi all,

When using pg_get_viewdef(oid), the view definition is returned in a
reconstructed form (I assume) with all formatting removed. This is a
pain for apps like pgAdmin, that allow the user to edit their views,
particularly with very large ones.

Would it be possible and sensible to store the original view definition
for future use, such as we do for functions? Perhaps a new catalog
(pg_source?) could store these and other definitions such as rules for
use?

Not too obvious, but this should be covered in the TODO item "Allow RULE
recompilation". That is because if the rule/view is broken due to other
schema changes, the reconstruction might fail. Thus we need to save a
textual form and the original CREATE statement from the user seems
logical.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:

Dave Page wrote:

Would it be possible and sensible to store the original view definition
for future use, such as we do for functions? Perhaps a new catalog
(pg_source?) could store these and other definitions such as rules for
use?

Not too obvious, but this should be covered in the TODO item "Allow RULE
recompilation". That is because if the rule/view is broken due to other
schema changes, the reconstruction might fail.

Given the dependency mechanism in 7.3, it should no longer be possible
to break a rule that way. Of course, there are cases where you'd wish
the rule to *change* not just reject the update.

The major problem with any such proposal is that source-form storage has
its own set of inflexibilities.  For example, we can currently allow
renaming of tables and columns that underlie a view, because the stored
form of the view doesn't contain those names and so it doesn't need to
change.  If we store source text then we'd have to forbid such renaming
--- or else update the source text, which seems to require parsing,
adjustment of parsed tree, deparsing; which rather defeats the purpose
of Dave's request.

There are even more subtle problems: the source text may be ambiguous
in some way, so that reparsing it today might not generate the identical
intepretation to what you had before. Even "a+b" is ambiguous given
the possibility that user-defined operators could be added, or the
search path changed. Deparsing compensates for this by producing (or
at least trying to produce) a representation that is correct and
unambiguous in the current context.

One reason I'm disillusioned with this idea is that we do take the
trouble to store both source and internal form of column default
expressions, but in practice pg_attrdef.adsrc has fallen into disuse.
That track record doesn't bode well for adding source-form storage of
other things.

regards, tom lane

Thanks for the responses. I guess I'm going to have to write some view
formatting code... :-(
Oh well, at least it's for a good reason!!

Regards, Dave

It's rumoured that Tom Lane once said:

Dave Page writes:

When using pg_get_viewdef(oid), the view definition is returned in a
reconstructed form (I assume) with all formatting removed. This is a
pain for apps like pgAdmin, that allow the user to edit their views,
particularly with very large ones.

Perhaps as a workaround you could invent a standard indentation format and
format the rules automatically that way, so that users will be able to
find everything in the same place automatically after the second edit
cycle.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

Perhaps as a workaround you could invent a standard indentation format and
format the rules automatically that way, so that users will be able to
find everything in the same place automatically after the second edit
cycle.

Perhaps we could make pg_get_ruledef and friends try to prettyprint
their output a little better, instead of duplicating such logic in
various clients (which couldn't do nearly as well at it anyway without
re-parsing the string :-().

Does anyone have code that depends on these functions returning
single-line output? (I suppose the pg_rules and pg_views views might
get a bit ugly, but perhaps psql could be taught to format data with
embedded newlines better than it does now...)

regards, tom lane

On Tue, 2003-04-01 at 14:18, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Perhaps as a workaround you could invent a standard indentation format and
format the rules automatically that way, so that users will be able to
find everything in the same place automatically after the second edit
cycle.

Perhaps we could make pg_get_ruledef and friends try to prettyprint
their output a little better, instead of duplicating such logic in
various clients (which couldn't do nearly as well at it anyway without
re-parsing the string :-().

Does anyone have code that depends on these functions returning
single-line output? (I suppose the pg_rules and pg_views views might
get a bit ugly, but perhaps psql could be taught to format data with
embedded newlines better than it does now...)

Could we turn the prettyprint incantation into a user callable function?
We could easily add that to the phpPgAdmin code to call that function
when pulling information from pg_views.definition and it's ilk, this
giving a nicely formatted output without having to modify the way it is
currently stored.

Robert Treat

Robert Treat <xzilla@users.sourceforge.net> writes:

On Tue, 2003-04-01 at 14:18, Tom Lane wrote:

Perhaps we could make pg_get_ruledef and friends try to prettyprint
their output a little better, instead of duplicating such logic in
various clients (which couldn't do nearly as well at it anyway without
re-parsing the string :-().

Could we turn the prettyprint incantation into a user callable function?

I don't think it's reasonable to implement the prettyprint as a separate
function, if that's what you mean. It would have to re-parse the string
which is exactly what I wanted to avoid. pg_get_ruledef itself already
knows what the statement structure is, and would need very little more
logic to do fairly reasonable pretty-printing.

We could make variants of pg_get_ruledef etc. that pretty-print,
while leaving the existing behavior alone, I suppose.

regards, tom lane

On Tue, 2003-04-01 at 14:18, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Perhaps as a workaround you could invent a standard indentation format and
format the rules automatically that way, so that users will be able to
find everything in the same place automatically after the second edit
cycle.

Perhaps we could make pg_get_ruledef and friends try to prettyprint
their output a little better, instead of duplicating such logic in
various clients (which couldn't do nearly as well at it anyway without
re-parsing the string :-().

Does anyone have code that depends on these functions returning
single-line output? (I suppose the pg_rules and pg_views views might
get a bit ugly, but perhaps psql could be taught to format data with
embedded newlines better than it does now...)

I do -- but the only thing it does is format the output to look better.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Perhaps we could make pg_get_ruledef and friends try to prettyprint
their output a little better, instead of duplicating such logic in
various clients (which couldn't do nearly as well at it anyway without
re-parsing the string :-().

Does anyone have code that depends on these functions returning
single-line output? (I suppose the pg_rules and pg_views views might
get a bit ugly, but perhaps psql could be taught to format data with
embedded newlines better than it does now...)

Well, phpPgAdmin kind of assumes that it does, however since it's written to a webpage, it will appear as one line anyway. I would need to just convert newlines to <br>s that's all.

The other dependency would be the new rule and trigger defs in that patch I sent in for psql recently. (And was committed).

Chris

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 01 April 2003 20:59
To: Robert Treat
Cc: Peter Eisentraut; Dave Page; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] View definition formatting

We could make variants of pg_get_ruledef etc. that
pretty-print, while leaving the existing behavior alone, I suppose.

Hacking those functions never crossed my mind, but it would certainly do
what I require, and is probably more within the scope of things I could
do myself. *If* I get time and can figure it out, how would people feel
about pg_get_pp_viewdef(oid) etc?

Regards, Dave.