From: Lee Kindness <lkindness@csl.co.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-ID: <15732.36384.130862.855693@kelvin.csl.co.uk>
Date: Tue, 3 Sep 2002 11:25:36 +0100
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
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 ]