Re: Structured Types, Oids and Reference Types

Richard

Thanks for replying. I probably forgot to mention that I am a masters student
at university and that I am strictly looking at PostgreSQL's Object
Relational features.

I am fairly competent at all the relational features like you have mentioned
but these are questions that I am a bit confused about because they are part
of SQL:99 standard (object relational) however I do not think PostgreSQL
supports some of these ideas I am exploring. I have done a lot of testing and
haven't been able to come up with a solution to my queries and I was
wondering if anyone had done something similar previously.

> 1) What collection types does PostgreSQL support, i.e. lists,
>
> > arrays, setof and bagof?
> > As far as I can see it supports arrays and multidimensional arrays. It
> > also supports setof(type) as a return type in a function.
>
> If you want to store multiple values, you almost certainly want to use
> multiple rows in a table.

Yes I agree but I specifically want to see if lists, arrays, setof or bagof
exist in the PostgreSQL environment.

> > CREATE TABLE Class_Nest(CourseID char(7), Year char(2), Students
> > setof(text));
>
> You want three tables here in a typical relational design: class, student
> and class_student to link the two.

Yes I agree with what you state above but what I am testing is whether or not
setof can be used in as a row type in a column of a table.

I don't think what I have done here works as I can't seem to find anywhere
about creation of a table with setof exists. According to the documentation I
can only see that setof is used

> > 3) What set oriented operations ( i.e. IN, SUBSET, UNION, INTERSECT,
> > EXCEPT...) are allowed on the collection types (i.e. lists, arrays, setof
> > and bagof)?
>
> Again, think more relational.

What I want to test here is whether or not these set oriented operations work
on any of the collection types lists, arrays, setof or bagof.

-------------------------------------------------------------------------
> > Oids and Reference Types
> > -------------------------------------------------------------------------
> >-- 1) Does PostgreSQL support tuple AND table oids? I believe tuple oids
> > are like unique ids for each row in a table/relation. However I am
> > confused over what a table oid is. Is a table oid an identifier for a
> > complete table/relation? e.g. if there were three tables A,B, and C then
> > we could assign 3 different oids to them say 1,2, and 3 respectively.
>
> There are OIDs for all objects, but it isn't recommended you use them
> yourself and they have become optional, possibly to be eliminated
> eventually.

The optional part can be specified using the "WITHOUT OIDS" command. There
seems no way to embed the oids in a table though, they are implicitly there.

So going back to table oids. How can you tell what the oid of a table is and
how can you refer to it?

> > 2) Can you dereference tuples or columns in a query by using a "deref"
> > function (DEREF is a keyword) for '.' or C type syntax '->'. i.e
>
> You're not thinking in relational terms again.

Yes I understand that. I specifically want to look at how to dereference an
object using an object relational database.

> > select e.Job->jobid
> > from Employee e;
> >
> > or
> >
> > select e.deref(Job).jobid
> > from Employee e;
> >
> > I can't seem to get either option to work.
>
> If you really want to do this you want:
>
> SELECT jobid FROM employee WHERE OID=12345;

Okay so how do I get all the oids, not just one oid? That is why I tried to
dereference the jobid.

> But, I have to ask why "jobid" isn't the key you are using to extract the
> data you want.

Looking at object relational features again.

> If you haven't got much experience with relational databases, I'd recommend
> a good primer (something by C J Date for the theory) and perhaps an
> introduction to Postgresql (Bruce's book is online for browsing - see the
> website for details).

Thankyou for your advice. I currently own Bruce's book and have the online
link as well.

One more question is the type of information that I am asking on the correct
mailing list?

Cheers Craig