Postgres Pro
Downloads
Home   >   mailing lists

Thread: Object features of pg

Object features of pg

From
Michael Ansley
Date:
<p><font size="2">I've done the following:</font><p><font size="2">create table address (address varchar(50), postcode
varchar(9));</font><br/><font size="2">create table client(id integer, name varchar(30), address
address);</font><p><fontsize="2">Now, how the hell do I get information into the address field of client?  There
appearsto be very little in the manual dealing with PGs object features.  Any pointers to places in the manual, or
directinstructions would be gratefully accepted.</font><p><font size="2">Cheers...</font><p><font size="2">MikeA</font>

Re: Object features of pg

From
Peter Mount
Date:
On Tue, 3 Oct 2000, Michael Ansley wrote:

> I've done the following:
> 
> create table address (address varchar(50), postcode varchar(9));
> create table client(id integer, name varchar(30), address address);
> 
> Now, how the hell do I get information into the address field of client?
> There appears to be very little in the manual dealing with PGs object
> features.  Any pointers to places in the manual, or direct instructions
> would be gratefully accepted.

Something like:

select id,name,a.address,postcode
from client c, address a
where c.address=a.oid;

Probably neater ways of doing it...

Peter

-- 
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

RE: Object features of pg

From
Michael Ansley
Date:
<p><font size="2">Hi, Peter,</font><p><font size="2">I suspected as much of the querying, but I can't even get data
intothe tables.  I tried this:</font><p><font size="2">insert into client values ('Michael');</font><br /><font
size="2">insertinto address values ('11 Windsor Close, 'RH16 4QR');</font><br /><font size="2">INSERT 18935 1</font><br
/><fontsize="2">update client set address = 18935 where name = 'Michael';</font><br /><font size="2">ERROR:  Attribute
'address'is of type 'address' but expression is of type 'int4</font><br /><font size="2">'</font><br /><font
size="2">       You will need to rewrite or cast the expression</font><p><font size="2">So that didn't work, and I
triedcasting the oid, but that didn't seem to work either.  Any ideas?</font><p><font size="2">Cheers...</font><br
/><br/><p><font size="2">>>   -----Original Message-----</font><br /><font size="2">>>   From: Peter Mount
[<ahref="mailto:peter@retep.org.uk">mailto:peter@retep.org.uk</a>]</font><br /><font size="2">>>   Sent: 03
October2000 14:32</font><br /><font size="2">>>   To: Michael Ansley</font><br /><font size="2">>>   Cc:
'pgsql-sql@postgresql.org'</font><br/><font size="2">>>   Subject: Re: [SQL] Object features of pg</font><br
/><fontsize="2">>>   </font><br /><font size="2">>>   </font><br /><font size="2">>>   On Tue, 3 Oct
2000,Michael Ansley wrote:</font><br /><font size="2">>>   </font><br /><font size="2">>>   > I've done
thefollowing:</font><br /><font size="2">>>   > </font><br /><font size="2">>>   > create table
address(address varchar(50), postcode varchar(9));</font><br /><font size="2">>>   > create table client(id
integer,name varchar(30), </font><br /><font size="2">>>   address address);</font><br /><font
size="2">>>  > </font><br /><font size="2">>>   > Now, how the hell do I get information into the
address</font><br /><font size="2">>>   field of client?</font><br /><font size="2">>>   > There appears
tobe very little in the manual dealing </font><br /><font size="2">>>   with PGs object</font><br /><font
size="2">>>  > features.  Any pointers to places in the manual, or </font><br /><font size="2">>>  
directinstructions</font><br /><font size="2">>>   > would be gratefully accepted.</font><br /><font
size="2">>>  </font><br /><font size="2">>>   Something like:</font><br /><font size="2">>>  
</font><br/><font size="2">>>   select id,name,a.address,postcode</font><br /><font size="2">>>   from
clientc, address a</font><br /><font size="2">>>   where c.address=a.oid;</font><br /><font size="2">>>  
</font><br/><font size="2">>>   Probably neater ways of doing it...</font><br /><font size="2">>>  
</font><br/><font size="2">>>   Peter</font><br /><font size="2">>>   </font><br /><font
size="2">>>  -- </font><br /><font size="2">>>   Peter T Mount peter@retep.org.uk <a
href="http://www.retep.org.uk"target="_blank">http://www.retep.org.uk</a></font><br /><font size="2">>>  
PostgreSQLJDBC Driver <a href="http://www.retep.org.uk/postgres/"
target="_blank">http://www.retep.org.uk/postgres/</a></font><br/><font size="2">>>   Java PDF Generator <a
href="http://www.retep.org.uk/pdf/"target="_blank">http://www.retep.org.uk/pdf/</a></font><br /><font
size="2">>>  </font><br /><font size="2">>>   </font>

RE: Object features of pg

From
Michael Ansley
Date:
Is anybody using the object features of PG?  We're busy evaluating databases for some prototyping projects, and relational, object and OR databases are all in the mix.  What I'm trying to do is to see how closely PG can match the object functionality of an object database.  I'm dead against going with an object database because I feel that the functionality that they provide is quite limiting.  In fact, you can't really do anything with an object unless you instantiate it, which is fine in some applications, but when you trying to process thousands of rows per second, it rather limiting.  Any views and opinions of people who have done (or tried to do) this would be most welcome.
 
Cheers...
 
 
MikeA
 
PS. Is anybody else having problems with the pgsql-general list, because I've sent about three messages to it, and it hasn't appeared.
 
 
-----Original Message-----
From: Michael Ansley [mailto:Michael.Ansley@intec-telecom-systems.com]
Sent: 03 October 2000 14:44
To: 'Peter Mount'
Cc: 'pgsql-sql@postgresql.org'
Subject: RE: [SQL] Object features of pg

Hi, Peter,

I suspected as much of the querying, but I can't even get data into the tables.  I tried this:

insert into client values ('Michael');
insert into address values ('11 Windsor Close, 'RH16 4QR');
INSERT 18935 1
update client set address = 18935 where name = 'Michael';
ERROR:  Attribute 'address' is of type 'address' but expression is of type 'int4
'
        You will need to rewrite or cast the expression

So that didn't work, and I tried casting the oid, but that didn't seem to work either.  Any ideas?

Cheers...

>>   -----Original Message-----
>>   From: Peter Mount [mailto:peter@retep.org.uk]
>>   Sent: 03 October 2000 14:32
>>   To: Michael Ansley
>>   Cc: 'pgsql-sql@postgresql.org'
>>   Subject: Re: [SQL] Object features of pg
>>  
>>  
>>   On Tue, 3 Oct 2000, Michael Ansley wrote:
>>  
>>   > I've done the following:
>>   >
>>   > create table address (address varchar(50), postcode varchar(9));
>>   > create table client(id integer, name varchar(30),
>>   address address);
>>   >
>>   > Now, how the hell do I get information into the address
>>   field of client?
>>   > There appears to be very little in the manual dealing
>>   with PGs object
>>   > features.  Any pointers to places in the manual, or
>>   direct instructions
>>   > would be gratefully accepted.
>>  
>>   Something like:
>>  
>>   select id,name,a.address,postcode
>>   from client c, address a
>>   where c.address=a.oid;
>>  
>>   Probably neater ways of doing it...
>>  
>>   Peter
>>  
>>   --
>>   Peter T Mount peter@retep.org.uk http://www.retep.org.uk
>>   PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
>>   Java PDF Generator http://www.retep.org.uk/pdf/
>>  
>>  

RE: Object features of pg

From
Michael Ansley
Date:
<p><font size="2">No.  I want the address attribute of the client class to be of type address, which is defined by the
addresstable.  Perhaps I should have named things a little more clearly ;-)</font><br /><p><font size="2">>>  
-----OriginalMessage-----</font><br /><font size="2">>>   From: Kenn Thompson [<a
href="mailto:KThompson@adestagroup.com">mailto:KThompson@adestagroup.com</a>]</font><br/><font size="2">>>  
Sent:03 October 2000 15:15</font><br /><font size="2">>>   To: Michael.Ansley@intec-telecom-systems.com</font><br
/><fontsize="2">>>   Subject: Re: [SQL] Object features of pg</font><br /><font size="2">>>   </font><br
/><fontsize="2">>>   </font><br /><font size="2">>>   Shouldn't that be </font><br /><font
size="2">>>  create table address (address varchar(50), postcode varchar(9));</font><br /><font
size="2">>>  create table client(id integer, name varchar(30), address </font><br /><font size="2">>>  
varchar(50);</font><br/><font size="2">>>   ?</font><br /><font size="2">>>   </font><br /><font
size="2">>>  >>> Michael Ansley </font><br /><font size="2">>>  
<Michael.Ansley@intec-telecom-systems.com>10/03/00 08:14AM >>></font><br /><font size="2">>>  
I'vedone the following:</font><br /><font size="2">>>   </font><br /><font size="2">>>   create table
address(address varchar(50), postcode varchar(9));</font><br /><font size="2">>>   create table client(id
integer,name varchar(30), address address);</font><br /><font size="2">>>   </font><br /><font
size="2">>>  Now, how the hell do I get information into the address </font><br /><font size="2">>>   field
ofclient?</font><br /><font size="2">>>   There appears to be very little in the manual dealing with </font><br
/><fontsize="2">>>   PGs object</font><br /><font size="2">>>   features.  Any pointers to places in the
manual,or direct </font><br /><font size="2">>>   instructions</font><br /><font size="2">>>   would be
gratefullyaccepted.</font><br /><font size="2">>>   </font><br /><font size="2">>>   Cheers...</font><br
/><fontsize="2">>>   </font><br /><font size="2">>>   MikeA</font><br /><font size="2">>>  
</font><br/><font size="2">>>   </font>

RE: Object features of pg

From
Michael Ansley
Date:
<p><font size="2">Hi, Kenn,</font><p><font size="2">>>   It seems that if you want to define a type address that
</font><br/><font size="2">>>   you will need more than a reference in another table. I </font><br /><font
size="2">>>  see what you're trying to do, but don't understand the </font><br /><font size="2">>>  
advantageof having a type address. Especially when you </font><br /><font size="2">>>   seem to be using standard
SQLto manipulate the data anyway.</font><br /><font size="2">I'm not sure how postgres does it, but I think that it
mightactually store the address in the address table, and use an oid to reference it from the client table.  The reason
fordoing this is that a) it matches the way an object orientated language would map it, and b) it should be a LOT
fasterthan using a foreign key lookup.  My understanding is that using OIDs is way faster than using FKs, which is
whereODBs get their speed from.  So, if I can do objects like ODBs in postgres then I get the speed of an ODB with the
funcionalityof a relational database (functions, sql, rules, etc.)</font><p><font size="2">Of course, I could just use
theaddress OID as an FK on client, but that would only really be going half the distance.</font><p><font
size="2">That'sthe plan, anyway.</font><p><font size="2">Cheers...</font><br /><br /><p><font size="2">>>  
</font><br/><font size="2">>>   >>> Michael Ansley </font><br /><font size="2">>>  
<Michael.Ansley@intec-telecom-systems.com>10/03/00 09:25AM >>></font><br /><font size="2">>>  
No. I want the address attribute of the client class to </font><br /><font size="2">>>   be of type
address,</font><br/><font size="2">>>   which is defined by the address table.  Perhaps I should </font><br
/><fontsize="2">>>   have named things a</font><br /><font size="2">>>   little more clearly ;-)</font><br
/><fontsize="2">>>   </font><br /><font size="2">>>   </font><br /><font size="2">>>   >>  
-----OriginalMessage-----</font><br /><font size="2">>>   >>   From: Kenn Thompson [<a
href="mailto:KThompson@adestagroup.com">mailto:KThompson@adestagroup.com</a>]</font><br /><font size="2">>>  
>>  Sent: 03 October 2000 15:15</font><br /><font size="2">>>   >>   To:
Michael.Ansley@intec-telecom-systems.com</font><br /><font size="2">>>   >>   Subject: Re: [SQL] Object
featuresof pg</font><br /><font size="2">>>   >>   </font><br /><font size="2">>>   >>  
</font><br/><font size="2">>>   >>   Shouldn't that be </font><br /><font size="2">>>   >>  
createtable address (address varchar(50), postcode </font><br /><font size="2">>>   varchar(9));</font><br
/><fontsize="2">>>   >>   create table client(id integer, name varchar(30), address </font><br /><font
size="2">>>  >>   varchar(50);</font><br /><font size="2">>>   >>   ?</font><br /><font
size="2">>>  >>   </font><br /><font size="2">>>   >>   >>> Michael Ansley </font><br
/><fontsize="2">>>   >>   <Michael.Ansley@intec-telecom-systems.com> 10/03/00 </font><br /><font
size="2">>>  08:14AM >>></font><br /><font size="2">>>   >>   I've done the
following:</font><br/><font size="2">>>   >>   </font><br /><font size="2">>>   >>   create
tableaddress (address varchar(50), postcode </font><br /><font size="2">>>   varchar(9));</font><br /><font
size="2">>>  >>   create table client(id integer, name varchar(30), </font><br /><font size="2">>>  
addressaddress);</font><br /><font size="2">>>   >>   </font><br /><font size="2">>>   >>  
Now,how the hell do I get information into the address </font><br /><font size="2">>>   >>   field of
client?</font><br/><font size="2">>>   >>   There appears to be very little in the manual dealing with
</font><br/><font size="2">>>   >>   PGs object</font><br /><font size="2">>>   >>   features. 
Anypointers to places in the manual, or direct </font><br /><font size="2">>>   >>   instructions</font><br
/><fontsize="2">>>   >>   would be gratefully accepted.</font><br /><font size="2">>>   >>  
</font><br/><font size="2">>>   >>   Cheers...</font><br /><font size="2">>>   >>   </font><br
/><fontsize="2">>>   >>   MikeA</font><br /><font size="2">>>   >>   </font><br /><font
size="2">>>  >>   </font><br /><font size="2">>>   </font>

RE: Object features of pg

From
Tod McQuillin
Date:
On Tue, 3 Oct 2000, Michael Ansley wrote:

> I suspected as much of the querying, but I can't even get data into the
> tables.  I tried this:
> 
> insert into client values ('Michael');
> insert into address values ('11 Windsor Close, 'RH16 4QR');
> INSERT 18935 1
> update client set address = 18935 where name = 'Michael';
> ERROR:  Attribute 'address' is of type 'address' but expression is of type
> 'int4
> '
>         You will need to rewrite or cast the expression

It worked for me (with 7.0.2 on linux/i386) when I tried it with a
cast like this:

update client set address = 18935::address where name = 'Michael';

I wasn't aware that a table name could be used as a type (which I assume
is represented as an integer internally).  Is that in the docs?

Probably it's better to use referential integrity checks if you want to
ensure type checking like this (it will also ensure that the value pointed
to actually exists in the referenced table).  (see
http://www.postgresql.org/docs/aw_pgsql_book/node153.html under PRIMARY
KEY for details).
-- 
Tod McQuillin


P.S.  If there is a one to one relationship between clients and addresses,
you probably don't really want to have them in separate tables.

It's more useful to have separate tables when there's a many-to-one
relationship, like when more than one client can have the same address, so
they both store a pointer to the same row in the address table.

But maybe that model doesn't work so well for addresses.  What if one of
the clients moves?  If you update the address record that another client
is also pointing to, it looks like they both moved.






> 
> So that didn't work, and I tried casting the oid, but that didn't seem to
> work either.  Any ideas?
> 
> Cheers...
> 
> 
> 
> >>   -----Original Message-----
> >>   From: Peter Mount [mailto:peter@retep.org.uk]
> >>   Sent: 03 October 2000 14:32
> >>   To: Michael Ansley
> >>   Cc: 'pgsql-sql@postgresql.org'
> >>   Subject: Re: [SQL] Object features of pg
> >>   
> >>   
> >>   On Tue, 3 Oct 2000, Michael Ansley wrote:
> >>   
> >>   > I've done the following:
> >>   > 
> >>   > create table address (address varchar(50), postcode varchar(9));
> >>   > create table client(id integer, name varchar(30), 
> >>   address address);
> >>   > 
> >>   > Now, how the hell do I get information into the address 
> >>   field of client?
> >>   > There appears to be very little in the manual dealing 
> >>   with PGs object
> >>   > features.  Any pointers to places in the manual, or 
> >>   direct instructions
> >>   > would be gratefully accepted.
> >>   
> >>   Something like:
> >>   
> >>   select id,name,a.address,postcode
> >>   from client c, address a
> >>   where c.address=a.oid;
> >>   
> >>   Probably neater ways of doing it...
> >>   
> >>   Peter
> >>   
> >>   -- 
> >>   Peter T Mount peter@retep.org.uk http://www.retep.org.uk
> >>   PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
> >>   Java PDF Generator http://www.retep.org.uk/pdf/
> >>   
> >>   
>

RE: Object features of pg

From
Peter Mount
Date:
On Tue, 3 Oct 2000, Michael Ansley wrote:

> Hi, Peter,
> 
> I suspected as much of the querying, but I can't even get data into the
> tables.  I tried this:
> 
> insert into client values ('Michael');
> insert into address values ('11 Windsor Close, 'RH16 4QR');
> INSERT 18935 1
> update client set address = 18935 where name = 'Michael';
> ERROR:  Attribute 'address' is of type 'address' but expression is of type
> 'int4
> '
>         You will need to rewrite or cast the expression
> 
> So that didn't work, and I tried casting the oid, but that didn't seem to
> work either.  Any ideas?

Hmmm, try:

update client set address = 18935::oid where name = 'Michael';

Peter

-- 
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/