Re: 7.3 gotchas for applications and client libraries

Tom/Hackers,

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

Tom Lane writes on 03 Sep 2002:> Lee Kindness <lkindness@csl.co.uk> writes:> >> > [ original post was regarding the
mileagein adding utility> >   functions to PostgreSQL to cut-out common catalog lookups, thus> >   making apps less
fragileto catalog changes ]> >> > CREATE OR REPLACE FUNCTION column_exists(NAME, NAME) RETURNS BOOLEAN AS '> > CREATE
ORREPLACE FUNCTION table_exists(NAME) RETURNS BOOLEAN AS'> >> > Obviously these need attention when our application
targets7.3 (and> > thanks for the heads-up), but all changes are localised.>> They are?  What will your policy be about
schemanames --- 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
wideenough 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
SELECTINTO 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.