Skip site navigation (1) Skip section navigation (2)

Re: 7.3 gotchas for applications and client libraries

From: Lee Kindness <lkindness(at)csl(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Lee Kindness <lkindness(at)csl(dot)co(dot)uk>, pgsql-hackers(at)postgresql(dot)org,pgsql-interfaces(at)postgresql(dot)org
Subject: Re: 7.3 gotchas for applications and client libraries
Date: 2002-12-02 16:30:15
Message-ID: 15851.35479.973766.789553@kelvin.csl.co.uk (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces
Tom/Hackers,

Going back a bit, but relevant with 7.3's release...

Tom Lane writes on 03 Sep 2002:
 > Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
 > >
 > > [ original post was regarding the mileage in adding utility
 > >   functions to PostgreSQL to cut-out common catalog lookups, thus
 > >   making apps less fragile to catalog changes ]
 > >
 > > CREATE OR REPLACE FUNCTION column_exists(NAME, NAME) RETURNS BOOLEAN AS '
 > > CREATE OR REPLACE FUNCTION table_exists(NAME) RETURNS BOOLEAN AS'
 > >
 > > Obviously these need attention when our application targets 7.3 (and
 > > thanks for the heads-up), but all changes are localised.
 >
 > They are?  What will your policy be about schema names --- won't you
 > have to touch every caller to add a schema name parameter?

As it turns out, no. And thinking about i'm sure this is right
approach too, assuming:

 CREATE SCHEMA a;
 CREATE SCHEMA b;
 CREATE TABLE a.foo(f1 INT,  f2 TEXT);
 CREATE TABLE b.foo(f1 TEXT, f2 NUMERIC(10,1));

then:

 SELECT column_exists('foo', 'f1');

should return 'f', however:

 SELECT column_exists('a.foo', 'f1');

should return 't', likewise with:

 SET SEARCH_PATH TO "a","public";
 SELECT column_exists('foo', 'f1');

I can't see any use in a separate parameter - the user will want the
current - in scope - table, or explicitly specify the schema with the
table name.

 > I'm not averse to trying to push logic over to the backend, but I think
 > the space of application requirements is wide enough that designing
 > general-purpose functions will be quite difficult.

On the whole I'd agree, but I think determining if a table/column
exists has quite a high usage... More so with things like
current_database() added to 7.3. Anyway, for reference here are
column_exists(table, column) and table_exists(table) functions for
PostgreSQL 7.3, changes from 7.3 version maked by ' -- PG7.3':

\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   pg_table_is_visible(c.oid) -- PG7.3
			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;
			AND   pg_table_is_visible(c.oid) -- PG7.3
		IF NOT FOUND THEN
			RETURN false;
		ELSE
			RETURN true;
		END IF;
	END;
' LANGUAGE 'plpgsql';

Of course, thanks for the original email in this thread:

 http://www.ca.postgresql.org/docs/momjian/upgrade_tips_7.3

Thanks, Lee Kindness.

In response to

pgsql-hackers by date

Next:From: Joe ConwayDate: 2002-12-02 16:56:41
Subject: Re: [GENERAL] One SQL to access two databases.
Previous:From: Vince VielhaberDate: 2002-12-02 15:51:29
Subject: Re: Postgres 7.3 announcement on postgresql.org

pgsql-interfaces by date

Next:From: Jeroen T. VermeulenDate: 2002-12-03 01:09:07
Subject: libpqxx testers needed!
Previous:From: Thomas A. LoweryDate: 2002-12-01 16:58:14
Subject: Re: ANNOUNCE: DBD::Pg 1.20

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group