From ken@sunward.org Fri Dec  23 18:24:12 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id DA3829DCD1F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 23 Dec 2005 14:24:12 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 14112-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 23 Dec 2005 14:24:12 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from anu.ie (mail.anu.net [213.247.49.236])
	by postgresql.org (Postfix) with ESMTP id 69B009DCD1A
	for <pgsql-sql@postgresql.org>; Fri, 23 Dec 2005 14:24:09 -0400 (AST)
Received: from kenxp (68.40.184.254) by anu.ie with ESMTP (Eudora Internet
	Mail Server X 3.2.8b1) for <pgsql-sql@postgresql.org>;
	Fri, 23 Dec 2005 18:24:09 +0000
From: "Ken Winter" <ken@sunward.org>
To: "PostgreSQL pg-sql list" <pgsql-sql@postgresql.org>
Subject: The Information Schema vs the PG Catalog
Date: Fri, 23 Dec 2005 13:24:03 -0500
Message-ID: <001801c607ee$0dbed960$6603a8c0@kenxp>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0019_01C607C4.24E8D160"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.021 required=5 tests=[AWL=0.020,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.021
X-Spam-Level: 
X-Archive-Number: 200512/204
X-Sequence-Number: 23746

This is a multi-part message in MIME format.

------=_NextPart_000_0019_01C607C4.24E8D160
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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. =20

=20

Which source is preferable?  Or if that answer isn't absolute, what are =
the
reasons or conditions for preferring one over the other? =20

=20

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.

=20

~ TIA

~ Ken


------=_NextPart_000_0019_01C607C4.24E8D160
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">


<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Verdana;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Verdana;
	color:windowtext;
	font-weight:normal;
	font-style:normal;
	text-decoration:none none;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>I&#8217;m writing PL/pgSQL routines that generate
triggers, functions, and rules based on design characteristics of =
tables, columns,
and other database objects.&nbsp; These routines need to be able to look =
up the
definitions of these objects.&nbsp; I see that there are two places =
available
to look up this info: the Information Schema and in the PG =
Catalog.&nbsp; </span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>Which source is preferable?&nbsp; Or if that answer =
isn&#8217;t
absolute, what are the reasons or conditions for preferring one over the =
other?&nbsp;
</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>Also, a specific question:&nbsp; Does the =
Information Schema
offer any way to list the sequences that exist and their =
attributes?&nbsp; I
can&#8217;t seem to find any.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>~ TIA</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DVerdana><span =
style=3D'font-size:10.0pt;
font-family:Verdana'>~ Ken</span></font></p>

</div>

</body>

</html>

------=_NextPart_000_0019_01C607C4.24E8D160--





From gpavlov@mynewplace.com Tue Dec  27 17:11:25 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id B654E9DC80C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Dec 2005 13:11:25 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 51300-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Dec 2005 13:11:24 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mis010.exch010.intermedia.net (mis010.exch010.intermedia.net
	[64.78.61.97]) by postgresql.org (Postfix) with ESMTP id 99F119DC879
	for <pgsql-sql@postgresql.org>; Tue, 27 Dec 2005 13:11:23 -0400 (AST)
Received: from ehost010-6.exch010.intermedia.net ([64.78.21.198]) by
	mis010.exch010.intermedia.net with Microsoft SMTPSVC(6.0.3790.1830);
	Tue, 27 Dec 2005 09:11:23 -0800
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: The Information Schema vs the PG Catalog
Date: Tue, 27 Dec 2005 09:11:20 -0800
Message-ID: 
 <CCB89282FCE1024EA3DCE687A96A516403895D2B@ehost010-6.exch010.intermedia.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Re: The Information Schema vs the PG Catalog
Thread-Index: AcYLCI6OvS0V0qBKS4KCCtqRjNhR5g==
From: "George Pavlov" <gpavlov@mynewplace.com>
To: <pgsql-sql@postgresql.org>
Cc: <ken@sunward.org>
X-OriginalArrivalTime: 27 Dec 2005 17:11:23.0163 (UTC)
	FILETIME=[9015CEB0:01C60B08]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/206
X-Sequence-Number: 23748

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=3D'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.
>=20
> Which source is preferable?  Or if that answer isn't absolute,
> what are the reasons or conditions for preferring one over the
> other?
>=20
> 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.
>=20



From ken@sunward.org Tue Dec  27 19:33:03 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 3C6C39DC815
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Dec 2005 15:33:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 75554-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Dec 2005 15:33:01 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from anu.ie (mail.anu.net [213.247.49.236])
	by postgresql.org (Postfix) with ESMTP id C4D289DC80C
	for <pgsql-sql@postgresql.org>; Tue, 27 Dec 2005 15:32:57 -0400 (AST)
Received: from kenxp (68.40.184.254) by anu.ie with ESMTP (Eudora Internet
	Mail Server X 3.2.8b1); Tue, 27 Dec 2005 19:32:57 +0000
From: "Ken Winter" <ken@sunward.org>
To: "'George Pavlov'" <gpavlov@mynewplace.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: The Information Schema vs the PG Catalog
Date: Tue, 27 Dec 2005 14:32:52 -0500
Message-ID: <005001c60b1c$54b86bc0$6603a8c0@kenxp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
In-Reply-To: 
 <CCB89282FCE1024EA3DCE687A96A516403895D2B@ehost010-6.exch010.intermedia.net>
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.034 required=5 tests=[AWL=0.034]
X-Spam-Score: 0.034
X-Spam-Level: 
X-Archive-Number: 200512/209
X-Sequence-Number: 23751

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.
> >