contribute pg_get_viewdef2 et al

Dave Page wrote:

>After a couple of releases of PostgreSQL you will start to see why a bit
>more clearly :-)
>
>Basically, things tend to get far more messy with every release because
>things in the database get moved around, new catalogues are added and
>old ones deprecated etc. In pgAdmin I, we used to make extensive use of
>functions and views on the server. They became an absolute nightmare to
>maintain requiring increasingly complex code to check and recreate them
>as required because we could never be sure that a user would
>upgrade/reinstall them following a server upgrade, or that they hadn't
>fiddled with them. Of course, that's ignoring the fact that each part of
>the system that could use alternate code got increasingly more complex,
>and you still ended up writing to the lowest common denominator anyway.
>
>
I can imagine... That's why I didn't rely on the existence of the
function, but have a fully functional fallback solution.

>Tom is being cautious and to be honest I'm on his side on this one. If a
>release goes out including functions used in the dumping of databases
>that do not work quite as they should because a few parentheses are
>missing, that'll be a major blot in PostgreSQL's reputation and is bound
>to spark up debate in places like /. where the MySQL fans will finally
>have something negative to say about PostgreSQL that is actually valid.
>
Yeah. All functions pgsql delivers must be 150 %, to keep the advantage
of reliability feature stability and maturity. I accept this as major
design goal; the MySQL guys should stay in BCCDWC-land
(By-Chance-Correct-Data-Who-Cares). That's why I proposed to take
STATEMENT TRIGGERS off the 7.4 announced features list (declaring them
experimental), because they aren't implemented completely (dataset
unknown to trigger function, provoking LOLs from other DBMS gurus). I
need these triggers soon, and eventually I'll have a look at completing
them myself. But so far, I'm in pgadmin3.

>
>I would suggest submitting your functions as patches to the backend that
>do all the reformatting but do not mess with the parentheses. I suspect
>this is the only way you'll get the code into either the main system or
>/contrib. Nobody seemed averse to that idea when I mentioned it on the
>list a while ago.
>
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. It
can't be done any better in the backend (except for exotic conditions,
e.g. distinguishing the ON SELECT/INSERT/UPDATE/DELETE TO expression in
pg_get_ruledef2 from the statement).
On the other side, parentheses optimization can't be done on the client
side without a complete sql parser (we don't want this in pgsql3, I
presume?), but it's easy with the backend's internal node evaluation.

Wanna see some parentheses orgy? This is real life:

pg_get_viewdef version:

SELECT ...
FROM (((((((((((((pt_partner par
JOIN pr_liste_partner rpl ON (((par.partner_nr )::numeric = (rpl.partner_nr )::numeric)))
JOIN pt_liste ptl ON (((ptl.liste_nr )::numeric = (rpl.liste_nr )::numeric)))
JOIN pv_anrede pva ON (((par.partner_nr )::numeric = (pva.partner_nr )::numeric)))
LEFT JOIN pr_partner_adresse rpa2 ON ((((par.partner_nr )::numeric = (rpa2.partner_nr )::numeric) AND ((rpa2.prior_adresse )::smallint = 1 ))))
LEFT JOIN pv_adresse adr2 ON (((rpa2.adresse_nr )::numeric = (adr2.adresse_nr )::numeric)))
LEFT JOIN pd_staat sta2 ON (((adr2.key_staat )::text = (sta2.key_staat )::text)))
LEFT JOIN ((pr_partner_komm rpe
JOIN pt_komm kom1 ON ((((rpe.komm_nr )::numeric = (kom1.komm_nr )::numeric) AND ((kom1.key_kommtyp )::integer = 8 ))))
JOIN pj_komm_email ema ON (((rpe.komm_nr )::numeric = (ema.komm_nr )::numeric)))
ON ((((par.partner_nr )::numeric = (rpe.partner_nr )::numeric) AND ((rpe.prior_email )::smallint = 1 ))))
LEFT JOIN (((pr_partner_komm rpt
JOIN pt_komm kom2 ON ((((rpt.komm_nr )::numeric = (kom2.komm_nr )::numeric) AND (((((((kom2.key_kommtyp )::integer = 1 ) OR ((kom2.key_kommtyp )::integer = 3 )) OR ((kom2.key_kommtyp )::integer = 4 )) OR ((kom2.key_kommtyp )::integer = 5 )) OR ((kom2.key_kommtyp )::integer = 7 )) OR ((kom2.key_kommtyp )::integer = 12 )))))
JOIN pj_komm_telefon tel ON (((rpt.komm_nr )::numeric = (tel.komm_nr )::numeric)))
JOIN pd_staat stt ON (((tel.key_staat )::text = (stt.key_staat )::text)))
ON ((((par.partner_nr )::numeric = (rpt.partner_nr )::numeric) AND ((rpt.prior_tel )::smallint = 1 ))))
LEFT JOIN (((pr_partner_komm rpf
JOIN pt_komm kom3 ON ((((rpf.komm_nr )::numeric = (kom3.komm_nr )::numeric) AND ((((kom3.key_kommtyp )::integer = 2 ) OR ((kom3.key_kommtyp )::integer = 6 )) OR ((kom3.key_kommtyp )::integer = 12 )))))
JOIN pj_komm_telefon fax ON (((rpf.komm_nr )::numeric = (fax.komm_nr )::numeric)))
JOIN pd_staat stf ON (((fax.key_staat )::text = (stf.key_staat )::text)))
ON ((((par.partner_nr )::numeric = (rpf.partner_nr )::numeric) AND ((rpf.prior_fax )::smallint = 1 ))))
LEFT JOIN pr_partner_partner prpp ON (((((prpp.partner_nr2 )::numeric = (par.partner_nr )::numeric) AND ((prpp.prior_partner )::smallint = 1 )) AND (prpp.key_partnerrolle IN (
SELECT pd_partnerrolle.key_partnerrolle
FROM pd_partnerrolle
WHERE (pd_partnerrolle.key_rollentyp IN (
SELECT pd_rollentyp.key_rollentyp
FROM pd_rollentyp
WHERE ((((pd_rollentyp.key_rollengruppe )::integer = 2 ) OR ((pd_rollentyp.key_rollengruppe )::integer = 3 )) OR ((pd_rollentyp.key_rollengruppe )::integer = 4 )))))))))
LEFT JOIN pv_anrede pva2 ON (((pva2.partner_nr )::numeric = (prpp.partner_nr )::numeric)))
LEFT JOIN pr_partner_adresse rpa3 ON ((((pva2.partner_nr )::numeric = (rpa3.partner_nr )::numeric) AND ((rpa3.prior_adresse )::smallint = 1 ))))
LEFT JOIN pv_adresse pvadr2 ON (((pvadr2.adresse_nr )::numeric = (rpa3.adresse_nr )::numeric)))
WHERE ((par.key_partnertyp )::integer = 1 )));

as opposed to pg_get_viewdef2 version:

SELECT ....
FROM pt_partner par
JOIN pr_liste_partner rpl ON par.partner_nr::numeric = rpl.partner_nr::numeric
JOIN pt_liste ptl ON ptl.liste_nr::numeric = rpl.liste_nr::numeric
JOIN pv_anrede pva ON par.partner_nr::numeric = pva.partner_nr::numeric
LEFT JOIN pr_partner_adresse rpa2 ON par.partner_nr::numeric = rpa2.partner_nr::numeric AND rpa2.prior_adresse::smallint = 1
LEFT JOIN pv_adresse adr2 ON rpa2.adresse_nr::numeric = adr2.adresse_nr::numeric
LEFT JOIN pd_staat sta2 ON adr2.key_staat::text = sta2.key_staat::text
LEFT JOIN (pr_partner_komm rpe
JOIN pt_komm kom1 ON rpe.komm_nr::numeric = kom1.komm_nr::numeric AND kom1.key_kommtyp::integer = 8
JOIN pj_komm_email ema ON rpe.komm_nr::numeric = ema.komm_nr::numeric )
ON par.partner_nr::numeric = rpe.partner_nr::numeric AND rpe.prior_email::smallint = 1
LEFT JOIN (pr_partner_komm rpt
JOIN pt_komm kom2 ON rpt.komm_nr::numeric = kom2.komm_nr::numeric AND (((((kom2.key_kommtyp::integer = 1 OR kom2.key_kommtyp::integer = 3 ) OR kom2.key_kommtyp::integer = 4 ) OR kom2.key_kommtyp::integer = 5 ) OR kom2.key_kommtyp::integer = 7 ) OR kom2.key_kommtyp::integer = 12 )
JOIN pj_komm_telefon tel ON rpt.komm_nr::numeric = tel.komm_nr::numeric
JOIN pd_staat stt ON tel.key_staat::text = stt.key_staat::text )
ON par.partner_nr::numeric = rpt.partner_nr::numeric AND rpt.prior_tel::smallint = 1
LEFT JOIN (pr_partner_komm rpf
JOIN pt_komm kom3 ON rpf.komm_nr::numeric = kom3.komm_nr::numeric AND ((kom3.key_kommtyp::integer = 2 OR kom3.key_kommtyp::integer = 6 ) OR kom3.key_kommtyp::integer = 12 )
JOIN pj_komm_telefon fax ON rpf.komm_nr::numeric = fax.komm_nr::numeric
JOIN pd_staat stf ON fax.key_staat::text = stf.key_staat::text )
ON par.partner_nr::numeric = rpf.partner_nr::numeric AND rpf.prior_fax::smallint = 1
LEFT JOIN pr_partner_partner prpp ON (prpp.partner_nr2::numeric = par.partner_nr::numeric AND prpp.prior_partner::smallint = 1 ) AND prpp.key_partnerrolle IN (
SELECT pd_partnerrolle.key_partnerrolle
FROM pd_partnerrolle
WHERE pd_partnerrolle.key_rollentyp IN (
SELECT pd_rollentyp.key_rollentyp
FROM pd_rollentyp
WHERE (pd_rollentyp.key_rollengruppe::integer = 2 OR pd_rollentyp.key_rollengruppe::integer = 3 ) OR pd_rollentyp.key_rollengruppe::integer = 4 ))
LEFT JOIN pv_anrede pva2 ON pva2.partner_nr::numeric = prpp.partner_nr::numeric
LEFT JOIN pr_partner_adresse rpa3 ON pva2.partner_nr::numeric = rpa3.partner_nr::numeric AND rpa3.prior_adresse::smallint = 1
LEFT JOIN pv_adresse pvadr2 ON pvadr2.adresse_nr::numeric = rpa3.adresse_nr::numeric
WHERE par.key_partnertyp::integer = 1 ));

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

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

Regards,
Andreas