Re: Identification of serial fields - Mailing list pgsql-hackers

From Carlos Guzman Alvarez
Subject Re: Identification of serial fields
Date
Msg-id 3F2C4C79.4050509@telefonica.net
Whole thread Raw
In response to Re: Identification of serial fields  (Carlos Guzman Alvarez <carlosga@telefonica.net>)
Responses Re: Identification of serial fields
List pgsql-hackers
Hello:

> Thanks very much i will try it and comment the results here :)

This is was i'm finally using for retirve column information:

SELECT pg_namespace.nspname AS TABLE_SCHEMA,
pg_class.relname AS TABLE_NAME,
pg_attribute.attname AS COLUMN_NAME,
pg_attribute.atttypid AS DATA_TYPE,
pg_attribute.attlen AS COLUMN_SIZE,
pg_attribute.attndims AS COLUMN_DIMENSIONS,
pg_attribute.attnum AS ORDINAL_POSITION,
pg_attribute.atthasdef AS HAS_DEFAULT,
pg_attrdef.adsrc AS COLUMN_DEFAULT,
pg_attribute.attnotnull AS IS_NOT_NULL,
(pg_depend.objid is not null) AS IS_AUTOINCREMENT,
case pg_attribute.attstorage  when 'p' THEN 'PLAIN' when 'e' THEN 
'EXTERNAL' when 'm' THEN 'MAIN' when 'x' THEN 'EXTENDED' END  AS STORAGE,
pg_description.description AS DESCRIPTION
FROM pg_attribute
left join pg_class ON pg_attribute.attrelid = pg_class.oid
left join pg_namespace ON pg_class.relnamespace = pg_namespace.oid
left join pg_attrdef ON (pg_class.oid = pg_attrdef.adrelid AND 
pg_attribute.attnum = pg_attrdef.adnum)
left join pg_description ON (pg_attribute.attrelid = 
pg_description.objoid AND pg_attribute.attnum = pg_description.objsubid) 
left join pg_depend ON (pg_attribute.attrelid = pg_depend.objid AND 
pg_depend.deptype = 'i')
WHERE pg_attribute.attisdropped = false AND pg_attribute.attnum > 0 
ORDER BY pg_namespace.nspname, pg_class.relname, pg_attribute.attnum

I'm not sure now if it's 100% correct but the first tests seems to work, 
i will try to make more tests tomorrow :)





-- 
Best regards

Carlos Guzmán Álvarez
Vigo-Spain



pgsql-hackers by date:

Previous
From: "Andrew Dunstan"
Date:
Subject: logging disconnections?
Next
From: des@des.no (Dag-Erling Smørgrav)
Date:
Subject: Re: AUTO_INCREMENT patch