pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: About primary keys -- made some progress
2+ messages / 2 participants
[nested] [flat]
* Re: About primary keys -- made some progress
@ 2003-08-16 04:15 Tim Andersen <timander37@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Tim Andersen @ 2003-08-16 04:15 UTC (permalink / raw)
To: pgsql-sql
This might not be the cleanest solution, but it runs
fast and it retrieved the information I need.
I broke it down into pieces and created several views
to query from to simplify it for myself.
The first four statements are views and the last one
is the query I was originally trying to get. (note
that smmtsys is a schema I created, everything else is
dealing with system catalog tables)
Here's the SQL:
---------------
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;
create view smmtsys.v_primarykeys as(
select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ta.attrelid, ia.attnum, i.indisprimary
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n , pg_tables
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
AND pg_tables.tablename = c.relname
AND (pg_tables.schemaname = 'summit' or
pg_tables.schemaname = 'uhelp' or
pg_tables.schemaname = 'smmtsys' or
pg_tables.schemaname = 'smmtsec' or
pg_tables.schemaname = 'smmtccon' )
and ta.attname > 0
)
;
create view smmtsys.v_allcolumns as (
select pg_tables.schemaname,
pg_tables.tablename,
pg_attribute.attname
from pg_tables,
pg_class,
pg_attribute,
smmtsys.v_datatype
where (schemaname = 'smmtccon' or
schemaname = 'smmtsec' or
schemaname = 'smmtsys' or
schemaname = 'summit' or
schemaname = 'uhelp' ) and
pg_class.relname = pg_tables.tablename and
pg_type.typname = pg_tables.tablename and
pg_attribute.attrelid = pg_class.relfilenode and
pg_attribute.attnum > 0 and
pg_attribute.atttypid = smmtsys.v_datatype.oid
)
;
create view smmtsys.v_primarykeyind as (
select cols.schemaname ,
cols.tablename ,
cols.attname,
case pks.indisprimary
when true then 'Y'
else 'N'
end as in_primary_key
from smmtsys.v_allcolumns cols left outer join
smmtsys.v_primarykeys pks
on (cols.schemaname = pks.schemaname
and cols.tablename = pks.tablename
and cols.attname= pks.attname)
);
select upper(tbls.schemaname) as "creator",
upper(tbls.tablename) as "tname",
upper(cols.attname) as "cname",
case smmtsys.v_datatype.typname
when 'bpchar' then 'char'
else smmtsys.v_datatype.typname
end as "coltype",
case cols.attnotnull
when true then 'N'
when false then 'Y'
end as "nulls",
length(cols.attrelid) as "length",
cols.attndims as "syslength",
vpk.in_primary_key,
cols.attnum as "colno"
from pg_tables tbls,
pg_class,
pg_attribute cols,
pg_type,
smmtsys.v_datatype,
smmtsys.v_primarykeyind vpk
where (tbls.schemaname = 'smmtccon'
or tbls.schemaname = 'smmtsec'
or tbls.schemaname = 'smmtsys'
or tbls.schemaname = 'summit'
or tbls.schemaname = 'uhelp')
and pg_class.relname = tbls.tablename
and pg_type.typname = tbls.tablename
and cols.attrelid = pg_class.relfilenode
and cols.attnum > 0
and cols.atttypid = smmtsys.v_datatype.oid
and vpk.schemaname = tbls.schemaname
and vpk.tablename = tbls.tablename
and vpk.attname = cols.attname
;
This retrieves all of the columns and shows a primary
key indicator for each column. If someone could put
this logic all into one SQL query, I'd really like to
see it!
I still have a question about how to get the
information about length and precision of a column
from pg_attributes.atttypmod. are there built-in
functions for PostgreSQL to extract this information?
Additionally, I need to get the column default value
and the comments on the column, but I think I can
figure that out with a little more time.
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: About primary keys -- made some progress
@ 2003-08-16 12:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tim Andersen <timander37@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-08-16 12:36 UTC (permalink / raw)
To: Tim Andersen <timander37@yahoo.com>; +Cc: pgsql-sql
Tim Andersen <timander37@yahoo.com> writes:
> I still have a question about how to get the
> information about length and precision of a column
> from pg_attributes.atttypmod. are there built-in
> functions for PostgreSQL to extract this information?
Best is to rely on the format_type() function. Also, have you thought
about using pg_get_indexdef() in place of all that hacking about in
pg_index?
http://www.postgresql.org/docs/7.3/static/functions-misc.html
In general, your code is less likely to break if you can use the
"catalog information functions" rather than poking around in the
catalogs directly.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-08-16 12:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-16 04:15 Re: About primary keys -- made some progress Tim Andersen <timander37@yahoo.com>
2003-08-16 12:36 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox