To: ch@rodos.fzk.de
Subject: Re: How to know column constraints via system catalog tables
Reply-To: so_excited@excite.com
From: "Damon Chong" <so_excited@excite.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Cc: pgsql-sql@postgresql.org
Message-Id: <20031112120448.CFA66B6C2@xmxpita.excite.com>
Date: Wed, 12 Nov 2003 07:04:48 -0500 (EST)


Thanks alot, you are right with the table and the -E option. It's very useful. 

The only issue I find with your SQL is it relies on "(a.attnum = x.conkey[1] or a.attnum = x.conkey[2])" which assumes there is two columns forming the primary key of a table. Perhaps, I should explain what I'm trying to achieve with this SQL.

I'm writing an abstraction layer (abit like persistent but less ambitious). I hope to make this C++ layer generic for any RDBMS as long as the RDMS allow discovery of a table's columns name and the columns' data type. The RDBMS should also expose the field(s) used to form the primary key of a table. This will free the programmer from coding the class data member to correspond to the underlying table's fields (automate those tedious tasks of mapping OO classes to database tables). 

I'm using libpqxx for postgreSQL, I had thought of a hack which is to strip the {1,2..} string returned by the conkey of pg_constraint to get the column numbers. It's not pretty as I have to execute at least two queries but it should be workable. 

Thanks.

---------------snip-------------------
First, do you know the psql -E option which shows you the SQL behind the \d outputs.
You have probably used this films table (there is more than one in the doc):
CREATE TABLE films (
code CHAR(5),
title VARCHAR(40),
did DECIMAL(3),
date_prod DATE,
kind VARCHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
Based on a query I'm using to retrieve column information
(improved by Tom Lane's help), I think I've found something useful. Try

SELECT
upper(u.usename) AS TBL_OWNER, upper(c.relname) AS TBL_NAME,
upper(a.attname) AS COL_NAME, a.atttypid AS COL_TYPE,
int4larger(a.attlen, a.atttypmod - 4) AS COL_LENGTH,
CASE WHEN a.attnotnull=true THEN 0 ELSE 1 END AS COL_NULL, a.attnum AS COL_SEQ,
CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d
WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN
1
ELSE
0
END AS COL_DEFAULT
from pg_attribute a, pg_constraint x,
pg_class c left join pg_user u on (u.usesysid = c.relowner)
where c.oid = a.attrelid and not (c.relname ~* 'pg_') and
c.relkind = 'r' and a.attnum > 0 and
c.relfilenode=x.conrelid and x.contype='p' and c.relname ='films' and
(a.attnum = x.conkey[1] or a.attnum = x.conkey[2]) ;

tbl_owner | tbl_name | col_name | col_type | col_length | col_null | col_seq | col_default
-----------+----------+----------+----------+------------+----------+---------+-------------
CH | FILMS | CODE | 1042 | 5 | 0 | 1 | 0
CH | FILMS | TITLE | 1043 | 40 | 0 | 2 | 0
(2 rows)

You'll probably want to get rid of some parts (e.g. the upper case conversion),
but basically it's what you were looking for. Right?


_______________________________________________
Join Excite! - http://www.excite.com
The most personalized portal on the Web!