Re: Structured Types, Oids and Reference Types - Mailing list pgsql-general

On Friday 04 Oct 2002 11:34 am, Craig Anslow wrote:
> 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.

Fair enough. Apologies if I made assumptions about your level of knowledge.

[snipped my comments]
> Yes I agree but I specifically want to see if lists, arrays, setof or bagof
> exist in the PostgreSQL environment.

The arrays you see are all there is. Their usage is somewhat limited at the
moment, afaik you can't check for the existence of a value anywhere within an
array, only check the nth value.

No lists, sets or bags I'm afraid. Most of the object-related stuff is the
older code iirc, there's not been too much development 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?

select oid from pg_class where relname = 'foo';

But I don't think it's going to help with what you want.

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

Nope - barring some feature I've not come across, you're not going to get
anywhere. I think you're looking for something that's just not in PG.

My understanding is that Postgresql's object features come from way back when
it was still being developed in academia (before the SQL parser even). What
you *can* do in PG is inherit one table (class) from another, to extend
person to cover lecturer and student for example.

You could also define your own types for setof etc. - I believe that's how
some of the specialised geographical stuff was introduced. Probably not what
you were looking to do I'm afraid.

What I don't think you'll find is the sort of object-relational stuff to tie
java/c++ object persistance directly into Postgresql. I'm afraid I don't know
enough about such stuff to be able to point you to somewhere else. Hopefully
someone else on the list can.

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

I think this list is as good as any. Possibly the interfaces list might be
useful too, but the developers pop up on general pretty frequently.

Again apologies for assumptions about your background. I read your message
thinking you were from a programming background and making your first venture
into RDBMS land.

- Richard Huxton