From: Lee Kindness <lkindness@csl.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org, pgsql-interfaces@postgresql.org, Lee Kindness <lkindness@csl.co.uk>
Subject: 7.3 gotchas for applications and client libraries
Date: Tue, 3 Sep 2002 11:25:36 +0100
Message-ID: <15732.36384.130862.855693@kelvin.csl.co.uk> (raw)
In-Reply-To: <851.1031018040@sss.pgh.pa.us>
References: <851.1031018040@sss.pgh.pa.us>
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 ]
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: lkindness@csl.co.uk, tgl@sss.pgh.pa.us
Subject: Re: 7.3 gotchas for applications and client libraries
In-Reply-To: <15732.36384.130862.855693@kelvin.csl.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox