Re: How to know column constraints via system catalog tables - Mailing list pgsql-sql

>
> The only issue I find with your SQL is it relies on "(a.attnum = x.conkey[1] or a.attnum = x.conkey[2])" which
assumesthereis two columns forming the primary key of a table. Perhaps, I should explain what I'm trying to achieve
withthisSQL.
 
>
Not exactly. Look at this 
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)
 
);
CREATE TABLE films_02 (   code        CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,   title       CHARACTER VARYING(40)
NOTNULL,   did         DECIMAL(3) NOT NULL,   date_prod   DATE,   kind        CHAR(10),   len         INTERVAL HOUR TO
MINUTE
);
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 like 'film%' 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 |           0CH        | FILMS    | TITLE    |     1043 |         40 |
     0 |       2 |           0CH        | FILMS_02 | CODE     |     1042 |          5 |        0 |       1 |
0
(3 rows)
My supposition was it's no sign of good design to form primary keys from more 
than two columns. 
As you can see primary keys on a single column will be listed as well. 
So, if you want to deal with wider keys, 
you can add "or a.attnum = x.conkey[3]" etc. 

> 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
als
o 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
ta
bles).
Good Idea. But I've found it's always a PITA to question system tables, 
no matter which RDBMS you're on. 


> 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
w
orkable.
Anyway. 

Regards, Christoph