Re: Structured Types, Oids and Reference Types

On Friday 04 Oct 2002 9:15 am, Craig Anslow wrote:
> Structured Types
> ---------------------------------------------------------------------------
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.

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

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

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

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

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

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

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

- Richard Huxton