I'm working on a new interface to PostgreSQL and having a difficult
time. Perhaps someone here can shed some light on the subject.

1.  Am I correct in thinking that the OID that I receive with the
RowDescription is the column type?  I have several of those defined from
the 7.3 source code.  Have those changed in the past and are they likely
to change in the future?  I have heard people talking about querying the
server for type names or type oid values but I can't believe it is that
difficult.

2.  How do I receive the column modifers such as not null, primary key,
indexed, etc.  I know RowDescription sends down type modifiers but on an
int4 column that field is always -1.  I use another client app to change
the column from allowing nulls to not allowing nulls (same value in
column). The client app as well as psql on Linux sees the change but my
interface still receives just -1 for the type modifier.  What am I
missing?

I have read through all the docs/faqs I can find and still have no good
idea about this.

If there is someplace that has the oids documented or the low-level
constants documented, I would love to see that.

Thanks
Reggie



Fri, 6 Dec 2002 08:55:24 -0600
"Reggie Burnett" <rykr@bellsouth.net>
new interface
On Fri, 2002-12-06 at 09:55, Reggie Burnett wrote:
> I'm working on a new interface to PostgreSQL and having a difficult
> time. Perhaps someone here can shed some light on the subject.
> 
> 1.  Am I correct in thinking that the OID that I receive with the
> RowDescription is the column type?  I have several of those defined from

The OID is just a relatively unique database wide row marker -- which
you cannot count on existing in user tables.  Nothing more or less.  In
some of the system tables it is used as a foreign key to records in
other system tables, but Oid by itself has no such meaning.

> 
> I have read through all the docs/faqs I can find and still have no good
> idea about this.

Take a look at the 'System Catalog' documentation, and in psql the -E
option will show you the 'system' queries used to retrieve the
information for display in psql.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
06 Dec 2002 10:19:04 -0500
Rod Taylor <rbt@rbt.ca>
Re: new interface


> -----Original Message-----
> From: Rod Taylor [mailto:rbt@rbt.ca]
> Sent: Friday, December 06, 2002 9:19 AM
> To: Reggie Burnett
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] new interface
> 
> On Fri, 2002-12-06 at 09:55, Reggie Burnett wrote:
> > I'm working on a new interface to PostgreSQL and having a difficult
> > time. Perhaps someone here can shed some light on the subject.
> >
> > 1.  Am I correct in thinking that the OID that I receive with the
> > RowDescription is the column type?  I have several of those defined
from
> 
> The OID is just a relatively unique database wide row marker -- which
> you cannot count on existing in user tables.  Nothing more or less.
In
> some of the system tables it is used as a foreign key to records in
> other system tables, but Oid by itself has no such meaning.

So if OID can't be counted on to be there 100% of the time, what do I
use to index the catalog to determine type, not null-ness, uniqueness,
etc?

> 
> >
> > I have read through all the docs/faqs I can find and still have no
good
> > idea about this.
> 
> Take a look at the 'System Catalog' documentation, and in psql the -E
> option will show you the 'system' queries used to retrieve the
> information for display in psql.


> 
> --
> Rod Taylor <rbt@rbt.ca>
> 
> PGP Key: http://www.rbt.ca/rbtpub.asc


Fri, 6 Dec 2002 09:20:27 -0600
"Reggie Burnett" <rykr@bellsouth.net>
Re: new interface
Let me see if I can add more information here.  PSQL appears to only
retrieve table metadata when \d is given.  For my interface, I will need
the ability to present every column as a proper system object (int, byte
array, currency, etc) depending on the db type.  I will not be able to
wait for the user of my interface to execute a method before retrieving
this data so I will need to automatically retrieve it every time.  

Having looked at the query issued by \d in PSQL, it would seem that I
would need to issue that same query for every column in my select.  I
say that because it contains a join of pg_class and pg_attribute.

Am I understanding this right?


> -----Original Message-----
> From: Rod Taylor [mailto:rbt@rbt.ca]
> Sent: Friday, December 06, 2002 9:19 AM
> To: Reggie Burnett
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] new interface
> 
> On Fri, 2002-12-06 at 09:55, Reggie Burnett wrote:
> > I'm working on a new interface to PostgreSQL and having a difficult
> > time. Perhaps someone here can shed some light on the subject.
> >
> > 1.  Am I correct in thinking that the OID that I receive with the
> > RowDescription is the column type?  I have several of those defined
from
> 
> The OID is just a relatively unique database wide row marker -- which
> you cannot count on existing in user tables.  Nothing more or less.
In
> some of the system tables it is used as a foreign key to records in
> other system tables, but Oid by itself has no such meaning.
> 
> >
> > I have read through all the docs/faqs I can find and still have no
good
> > idea about this.
> 
> Take a look at the 'System Catalog' documentation, and in psql the -E
> option will show you the 'system' queries used to retrieve the
> information for display in psql.
> 
> --
> Rod Taylor <rbt@rbt.ca>
> 
> PGP Key: http://www.rbt.ca/rbtpub.asc


Fri, 6 Dec 2002 09:30:10 -0600
"Reggie Burnett" <rykr@bellsouth.net>
Re: new interface
On Fri, 2002-12-06 at 10:30, Reggie Burnett wrote:
> Let me see if I can add more information here.  PSQL appears to only
> retrieve table metadata when \d is given.  For my interface, I will need
> the ability to present every column as a proper system object (int, byte
> array, currency, etc) depending on the db type.  I will not be able to
> wait for the user of my interface to execute a method before retrieving
> this data so I will need to automatically retrieve it every time.  

> Having looked at the query issued by \d in PSQL, it would seem that I
> would need to issue that same query for every column in my select.  I
> say that because it contains a join of pg_class and pg_attribute.

Something like that I would suspect.

But if they're arbitrary selects, how are you going to handle:

SELECT CAST(32 AS text);

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
06 Dec 2002 10:41:19 -0500
Rod Taylor <rbt@rbt.ca>
Re: new interface