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.