Tom, do you think there is millage in adding functions (at least to
contrib) to PostgreSQL to avoid some of the common tasks applications
look into pg_* for?
For example I recently audited our code here for pg_* access, and
managed to create two plpgsql functions to replace all
occurrences. They were relatively simple queries to check if a table
existed and to check if a column existed, functions for 7.2.x:
\echo creating function: column_existsCREATE OR REPLACE FUNCTION column_exists(NAME, NAME) RETURNS BOOLEAN AS 'DECLARE
tab ALIAS FOR $1; col ALIAS FOR $2; rec RECORD;BEGIN SELECT INTO rec * FROM pg_class c, pg_attribute a
WHERE c.relname = tab AND c.oid = a.attrelid AND a.attnum > 0 AND a.attname =
col; IF NOT FOUND THEN RETURN false; ELSE RETURN true; END IF;END;' LANGUAGE 'plpgsql';
\echo creating function: table_existsCREATE OR REPLACE FUNCTION table_exists(NAME) RETURNS BOOLEAN AS 'DECLARE tab
ALIASFOR $1; rec RECORD;BEGIN SELECT INTO rec * FROM pg_class c WHERE c.relname = tab; IF NOT
FOUNDTHEN RETURN false; ELSE RETURN true; END IF;END;' LANGUAGE 'plpgsql';
Obviously these need attention when our application targets 7.3 (and
thanks for the heads-up), but all changes are localised. Surely these
must be fairly common tests and maybe better added to the database
server so applications are less dependant on internal catalogues?
Any desire for me to polish these two functions up for contrib in 7.3?
Actually the Cookbook at http://www.brasileiro.net/postgres/ has
similar function which will need attention for 7.3 too, is the
eventual plan for this to be folded into the core release?
Thanks, Lee.
Tom Lane writes:> Bruce suggested that we need a porting guide to help people look for> application and client-library
codethat will be broken by the changes> in PG 7.3. Here is a first cut at documenting the issues.> Comments welcome
---in particular, what have I missed?> [snip ]