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_exists
CREATE 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_exists
CREATE OR REPLACE FUNCTION table_exists(NAME) RETURNS BOOLEAN AS '
DECLARE
tab ALIAS FOR $1;
rec RECORD;
BEGIN
SELECT INTO rec *
FROM pg_class c
WHERE c.relname = tab;
IF NOT FOUND THEN
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 code that 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 ]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
