Oracle "describe" in postgres..

Oracle "describe" in postgres..

am 19.07.2004 07:29:01 von ohmy9od

I've got an Oracle 'describe'-inspired query that gives me the
following information on a table: the fields it includes with their
names, data types, lengths if indicated (otherwise '-1'), and if or if
not the field is not null. I'm getting this info with the following:

SELECT pg_attribute.attname as field,
pg_type.typname as type,
pg_type.typlen as length,
pg_attribute.attnotnull as isnotnull
FROM pg_class, pg_attribute, pg_type
WHERE pg_class.relname = 'online_pct'
AND pg_class.oid = pg_attribute.attrelid
AND pg_type.oid = pg_attribute.atttypid
AND NOT pg_attribute.attisdropped
AND pg_attribute.attnum > 0
ORDER BY pg_attribute.attnum;

I want this to include default values, if indicated, for each of the
fields. I've got this currently:

SELECT pg_attribute.attname as field,
pg_type.typname as type,
pg_type.typlen as length,
pg_attribute.attnotnull as isnotnull,
pg_attrdef.adsrc as defval
FROM pg_class, pg_attribute, pg_type, pg_attrdef
WHERE pg_class.relname = 'online_pct'
AND pg_class.oid = pg_attribute.attrelid
AND pg_type.oid = pg_attribute.atttypid
AND pg_class.oid = pg_attrdef.adrelid
AND pg_attribute.attnum = pg_attrdef.adnum
AND NOT pg_attribute.attisdropped
AND pg_attribute.attnum > 0
ORDER BY pg_attribute.attnum;

The issue here of course is that I now only get those fields for which
a default value is identified; otherwise the field is ignored because
there is no pg_class.oid = pg_attrdef.adrelid or pg_attribute.attnum =
pg_attrdef.adnum.

In general, I would include an if loop to only get the default value
if one were found, otherwise not; but in this case I'd like the query
to work by itself (no if loop I guess). I've essentially got to figure
out a way to include the default value; but to ignore it if it isn't
there - or, in other words, ignore it and its conditions in the where
clause (the ones including pg_attrdef) in cases where there is no
default value on a given field, in order to get those fields in
output, too.

Is there a way to get this to work? I'd be grateful for any input on
this..

- Oeln