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(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.