pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedThe Information Schema vs the PG Catalog
3+ messages / 2 participants
[nested] [flat]
* The Information Schema vs the PG Catalog
@ 2005-12-23 18:24 Ken Winter <ken@sunward.org>
0 siblings, 0 replies; 3+ messages in thread
From: Ken Winter @ 2005-12-23 18:24 UTC (permalink / raw)
To: pgsql-sql
I'm writing PL/pgSQL routines that generate triggers, functions, and rules
based on design characteristics of tables, columns, and other database
objects. These routines need to be able to look up the definitions of these
objects. I see that there are two places available to look up this info:
the Information Schema and in the PG Catalog.
Which source is preferable? Or if that answer isn't absolute, what are the
reasons or conditions for preferring one over the other?
Also, a specific question: Does the Information Schema offer any way to
list the sequences that exist and their attributes? I can't seem to find
any.
~ TIA
~ Ken
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: The Information Schema vs the PG Catalog
@ 2005-12-27 17:11 George Pavlov <gpavlov@mynewplace.com>
0 siblings, 1 reply; 3+ messages in thread
From: George Pavlov @ 2005-12-27 17:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: ken@sunward.org
I would say that pg_catalog is the more complete one whereas the
information_schema the more generic, standards-conformant place. I would
stick with the information_schema unless that becomes inadequate. A case
in point may be sequences. Apart from
information_schema.columns.column_default I haven't seen them
represented anywhere there (please someone correct me if I am wrong).
You can get more information about sequences from pg_catalog.pg_class
(look for pg_class.relkind='S') and various views that sit on top of
that (e.g. pg_statio_all_sequences).
George
> I'm writing PL/pgSQL routines that generate triggers, functions,
> and rules based on design characteristics of tables, columns, and
> other database objects. These routines need to be able to look up
> the definitions of these objects. I see that there are two places
> available to look up this info: the Information Schema and in the
> PG Catalog.
>
> Which source is preferable? Or if that answer isn't absolute,
> what are the reasons or conditions for preferring one over the
> other?
>
> Also, a specific question: Does the Information Schema offer any
> way to list the sequences that exist and their attributes? I
> can't seem to find any.
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: The Information Schema vs the PG Catalog
@ 2005-12-27 19:32 Ken Winter <ken@sunward.org>
parent: George Pavlov <gpavlov@mynewplace.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ken Winter @ 2005-12-27 19:32 UTC (permalink / raw)
To: 'George Pavlov' <gpavlov@mynewplace.com>; pgsql-sql
Thanks, George. What you say fits with what I was finding. I think that's
the way I will go.
~ Ken
> -----Original Message-----
> From: George Pavlov [mailto:gpavlov@mynewplace.com]
> Sent: Tuesday, December 27, 2005 12:11 PM
> To: pgsql-sql@postgresql.org
> Cc: ken@sunward.org
> Subject: Re: The Information Schema vs the PG Catalog
>
> I would say that pg_catalog is the more complete one whereas the
> information_schema the more generic, standards-conformant place. I would
> stick with the information_schema unless that becomes inadequate. A case
> in point may be sequences. Apart from
> information_schema.columns.column_default I haven't seen them
> represented anywhere there (please someone correct me if I am wrong).
> You can get more information about sequences from pg_catalog.pg_class
> (look for pg_class.relkind='S') and various views that sit on top of
> that (e.g. pg_statio_all_sequences).
>
> George
>
>
> > I'm writing PL/pgSQL routines that generate triggers, functions,
> > and rules based on design characteristics of tables, columns, and
> > other database objects. These routines need to be able to look up
> > the definitions of these objects. I see that there are two places
> > available to look up this info: the Information Schema and in the
> > PG Catalog.
> >
> > Which source is preferable? Or if that answer isn't absolute,
> > what are the reasons or conditions for preferring one over the
> > other?
> >
> > Also, a specific question: Does the Information Schema offer any
> > way to list the sequences that exist and their attributes? I
> > can't seem to find any.
> >
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-27 19:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-23 18:24 The Information Schema vs the PG Catalog Ken Winter <ken@sunward.org>
2005-12-27 17:11 Re: The Information Schema vs the PG Catalog George Pavlov <gpavlov@mynewplace.com>
2005-12-27 19:32 ` Ken Winter <ken@sunward.org>
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