Обсуждение: Structured Types, Oids and Reference Types
Hi I would like to know if anyone could answer my questions: ------------------------------------------------------------------------------------- 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. e.g. CREATE TABLE Class (CourseId char(7), Year char(2), Students text[]); However below does not work with setof(text): CREATE TABLE Class_Nest(CourseID char(7), Year char(2), Students setof(text)); 2) How to iterate through the collection type? Which is I presume is just arrays. I can currently only access one element at a time in the array but would like to know how to iterate through an array? 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)? I can't get anyone them to work on a collection type of an array. ------------------------------------------------------------------------------------- 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. 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 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. Cheers Craig Email: craig@mcs.vuw.ac.nz
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
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
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
Richard > Fair enough. Apologies if I made assumptions about your level of knowledge. That is fine I guess I didn't state my requirements clearly as this is the first time I have used any of the PostgreSQL lists. I have been using PostgreSQL for about 2 years. I also forgot to mention that I am emailing from New Zealand and that when you work I sleep and when I work you sleep, so that is one reason why I have probably not used the lists before, plus I have figured out most things that I needed to so far to do with relational features. > > 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. That solves that problem then. > 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. Maybe someone might implement these collection types one day. > > 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. I'll have to make some more tests and then I will let you know. > > > > 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. I guess you are probably right because I have not seemed to be able to get it to work. However it is like the bug issue in programs, "you can't show that a program is bug-free, testing only shows the presence of bugs". > 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. As part of one of masters projects I am looking at PostgreSQL's object relational features. Yeah I figured that inheritance issue out, that was straight forward. > 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. Yes you are right I just want to see if the collection types: list, bagof, setof and arrays are already implemented in PostgreSQL. > 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. Okay we will see then. > 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. I might give interfaces list a go. > 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. That is fine. Cheers Craig Email: craig@mcs.vuw.ac.nz Web: http://www.mcs.vuw.ac.nz/~craig