Обсуждение: Object-relational features

Hello,
For my object-relational database class I decided to use PostgreSQL
because it is my favorite database and it calls it self a ORDBMS.  Little
did I know that it supports supports very little OR features.  For
example, using "create type as" is totally worthless because you can't use
it as a field type in a table; you can't compose in another "create type
as"; and you can't inherit another composite type.  The only way to create
a true type is to use "create type" and write C code as a shared object,
so I'm basically doing everything C, which is not something I want to do.
I've searched the mailing lists and have found little said about the OR
features.  Am I missing something here?  Does PostgreSQL support OR
features similar to Oracle 9i (which is what I'm forced to use).  I really
do not want to use Oracle because I have to switch over to my Windows
partition, and Oracle takes about 100 MB of virtual memory on my 256 MB
machine.
Thanks,
Yasir

On Mar 13, 2004, at 12:30 PM, Yasir Malik wrote:

> For
> example, using "create type as" is totally worthless because you can't 
> use
> it as a field type in a table; you can't compose in another "create 
> type
> as"; and you can't inherit another composite type.  The only way to 
> create
> a true type is to use "create type" and write C code as a shared 
> object,
> so I'm basically doing everything C, which is not something I want to 
> do.


I'm not sure if this is what you are looking for, but it shows how to 
create a column type based on the text type. So your selects will 
return the column type as your custom type and you can process the 
content accordingly. From reading the docs (and asking on the list) I 
did not think this was possible either without writing external code in 
C. But a post about something else finally provided the clues I needed 
to get it working.

Best,

John DeSoi, Ph.D.


====
test=# create or replace function lispin(cstring, oid, int4) returns 
lisp as 'varcharin' language 'internal' immutable strict;
NOTICE:  type "lisp" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION
test=# create or replace function lispout(lisp) returns cstring as 
'varcharout' language 'internal' immutable strict;
NOTICE:  argument type lisp is only a shell
CREATE FUNCTION
test=# create type lisp (input=lispin, output=lispout, 
internallength=variable);
CREATE TYPE
test=# create table tst (a lisp);
CREATE TABLE
test=# insert into tst (a) values ('1');
INSERT 18499 1
test=# insert into tst (a) values ('(+ 5 5)');
INSERT 18500 1
test=# select * from tst;    a
--------- 1 (+ 5 5)
(2 rows)

Dr. DeSoi
Thanks for your reply.
What is an "immutable strict" and what is an "internal" language?

Does Postgres plan on implenting types as Oracle does?  For example:
create type AddrType as (street char(20), city char(20), zip char(5));
create type CustType as (name varchar(2), addr AddrType);
create table Cust of CustType;

Which is superior to Postgres's way of implementing types (writing a C
function).

Thanks,
Yasir

On Mon, 15 Mar 2004, John DeSoi wrote:

> Date: Mon, 15 Mar 2004 08:28:50 -0500
> From: John DeSoi <jd@icx.net>
> To: Yasir Malik <ymalik@cs.stevens-tech.edu>
> Cc: PostgreSQL <pgsql-sql@postgresql.org>
> Subject: Re: [SQL] Object-relational features
>
>
> On Mar 13, 2004, at 12:30 PM, Yasir Malik wrote:
>
> > For
> > example, using "create type as" is totally worthless because you can't
> > use
> > it as a field type in a table; you can't compose in another "create
> > type
> > as"; and you can't inherit another composite type.  The only way to
> > create
> > a true type is to use "create type" and write C code as a shared
> > object,
> > so I'm basically doing everything C, which is not something I want to
> > do.
>
>
> I'm not sure if this is what you are looking for, but it shows how to
> create a column type based on the text type. So your selects will
> return the column type as your custom type and you can process the
> content accordingly. From reading the docs (and asking on the list) I
> did not think this was possible either without writing external code in
> C. But a post about something else finally provided the clues I needed
> to get it working.
>
> Best,
>
> John DeSoi, Ph.D.
>
>
> ====
> test=# create or replace function lispin(cstring, oid, int4) returns
> lisp as 'varcharin' language 'internal' immutable strict;
> NOTICE:  type "lisp" is not yet defined
> DETAIL:  Creating a shell type definition.
> CREATE FUNCTION
> test=# create or replace function lispout(lisp) returns cstring as
> 'varcharout' language 'internal' immutable strict;
> NOTICE:  argument type lisp is only a shell
> CREATE FUNCTION
> test=# create type lisp (input=lispin, output=lispout,
> internallength=variable);
> CREATE TYPE
> test=# create table tst (a lisp);
> CREATE TABLE
> test=# insert into tst (a) values ('1');
> INSERT 18499 1
> test=# insert into tst (a) values ('(+ 5 5)');
> INSERT 18500 1
> test=# select * from tst;
>      a
> ---------
>   1
>   (+ 5 5)
> (2 rows)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

On Mon, Mar 15, 2004 at 09:06:39 -0500, Yasir Malik <ymalik@cs.stevens-tech.edu> wrote:
> Dr. DeSoi
> Thanks for your reply.
> What is an "immutable strict" and what is an "internal" language?

"immutable" means the function will ALWAYS return the same value when
called with the same arguments. "strict" means that the function will
return NULL if any of its arguments are NULL.

On Sat, 13 Mar 2004, Yasir Malik wrote:

> For my object-relational database class I decided to use PostgreSQL
> because it is my favorite database and it calls it self a ORDBMS.  Little
> did I know that it supports supports very little OR features.  For
> example, using "create type as" is totally worthless because you can't use
> it as a field type in a table; you can't compose in another "create type
> as"; and you can't inherit another composite type.  The only way to create
> a true type is to use "create type" and write C code as a shared object,
> so I'm basically doing everything C, which is not something I want to do.
> I've searched the mailing lists and have found little said about the OR
> features.  Am I missing something here?  Does PostgreSQL support OR
> features similar to Oracle 9i (which is what I'm forced to use).  I really
> do not want to use Oracle because I have to switch over to my Windows
> partition, and Oracle takes about 100 MB of virtual memory on my 256 MB
> machine.

Generally speaking many of the OR portions of PostgreSQL have fallen by
the side for lack of alot of developer support.  It generally needs
someone championing it who's willing to do some of the work and probably
take some lumps on -hackers trying to get stuff accepted.