Re: contribute pg_get_viewdef2 et al - Mailing list pgadmin-hackers

> -----Original Message-----
> From: Andreas Pflug [mailto:Andreas.Pflug@web.de]
> Sent: 07 May 2003 12:54
> To: Dave Page; pgadmin-hackers@postgresql.org; Tom Lane
> Subject: contribute pg_get_viewdef2 et al
>
>
> Dave Page wrote:
>
> I can imagine... That's why I didn't rely on the existence of the
> function, but have a fully functional fallback solution.

Which is good, but does make the code more complex. More importantly,
consider what could happen if the user did a database upgrade to a
version with different catalogues or whatever, but didn't upgrade the
extra functions to new versions, just left the old libs there and
reloaded their database. Or if we used a view or a plpgsql function that
had the same problem.

I'm not trying to be a pain in the *&% but I've been here before and
don't particularly want to go through the ensuing pain again.

> >
> Well that doesn't make sense at all. Indentation and line
> formatting can
> be done quite well on the client side, as it is implemented now.

I never really go a chance to look at the backend code, but I kinda
figured it builds the SQL in a vaguely similar way to what we do (though
obviously getting the info from the internal representation of the
object rather than the system catalogues), and that the new functions
could do the same just with appropriate \n's and spaces added for
formatting.

Or does pg_get_xxxdef2 just reformat pg_getxxxdef's output?

<snip views>

> The second version will create exactly the same plan as the
> first, and
> this view isn't the largest view I have to deal with...
> As you can see, there are still some parentheses which could
> be omitted
> (e.g. with multiple bool expressions), but the defensive
> strategy says
> "if not sure, put them in".

I agree your example is, umm, icky, but can you prove that your patch
will not misintepret anything and produce bad output? Once again, isn't
this a case of playing it safe?

> It would even be desirable to reduce the
> number of casts, but unfortunately implicit and explicit
> casts cannot be
> distinguished so that would be really unsafe.

Yes, I do find them annoying sometimes.

> Still, the latter is the second best version: retrieving the original
> source including formatting and comments is what I really want.

Jean-Michel did something like that in an unreleased version of pgAdmin
I. He stored object definitions in the database, and implemented some
dependency tracking to allow much more flexible editting of database
objects. As I recall it worked quite well, but could be made to fall
over if you really tried.

In that case storing the code worked because there were very few ALTER
TABLE options in those days, and no CREATE OR REPLACES, and the code
generally worked by completely recreating the modified object each time
- which the user would always do through pgAdmin. Storing the original
SQL in the catalogues however won't work because you have to let the
user use whatever interface they like, and give them access to the ALTER
statements. This of course means that your SQL is no longer correct as
soon as someone renames a column or table, or performs any such
modification of a named object.

Regards, Dave.