[SQL] Re: system catalog info
hi Ron, Try the developers manual: http://www.postgresql.org/devel-corner/docs/postgres/developer.htm actual catalog info is: http://postgresql.mirror.aarnet.edu.au/devel-corner/docs/postgres/catalogs.htm If you find any other sources please let me know - especially any that refer to composite types ;-) cheers, John Ron Peterson wrote: > > The HTML programming documentation (e.g. > http://www.postgresql.org/devel-corner/docs/programmer/pg-system-catalogs.htm) > indicates that more extensive information about the system catalogs can > be found in the "Reference Manual". Where can this reference manual be > found? Or where can more extensive documentation about the system > catalogues be found? > > -Ron- -- ------ john reid e-mail [EMAIL PROTECTED] technical officerroom G02, building 41 school of geosciences phone +61 02 4221 3963 university of wollongong fax +61 02 4221 4250 uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
[SQL] abstract data types?
Hi all,
I'm sure this has become somewhat of a FAQ recently, but I couldn't
find any reference to casting composite types in the mailing lists.
I'm trying to figure out what support PostgreSQL offers for SQL99
abstract data types.
PostgreSQL version is cvs from about a week ago.
Given the following relations:
test=> \d c_adtattr
Table "c_adtattr"
Attribute | Type | Modifier
---+--+--
attr1 | text |
attr2 | text |
test=> \d c_adtparent
Table "c_adtparent"
Attribute | Type| Modifier
---+---+--
basetype | text |
adtattr | c_adtattr |
OK, now try and insert a tuple into the relation with the composite
attribute:
test=> INSERT INTO c_adtparent values ('basetype','{"adtr1a1","adtr1a2"}');
ERROR: Attribute 'adtattr' is of type 'c_adtattr' but expression is of
type 'unknown'
You will need to rewrite or cast the expression
Is this use of the '{" delimiters correct? I took it from the array
docs, which was only reference that I could find to anything like
inserting values into composite types.
OK, try something stupid (cast as pg_type.typname):
test=> INSERT INTO c_adtparent values ('basetype', CAST
('{"adtr1a1","adtr1a2"}' AS c_adtattr));
ERROR: pg_atoi: error in "{"adtr1a1","adtr1a2"}": can't parse
"{"adtr1a1","adtr1a2"}"
OK, try insert into individual attributes:
test=> INSERT INTO c_adtparent (basetype, adtattr.attr1, adtattr.attr2)
VALUES ('basetype', CAST ('adtr1a1') AS text, CAST ('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
OK, try double dot syntax from SQL99:
test=> INSERT INTO c_adtparent (basetype, adtattr..attr1,
adtattr..attr2) VALUES ('basetype', CAST ('adtr1a1') AS text, CAST
('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
So far, so bad. Am I doing something really stupid with the syntax,
should I be using a different approach, or does the support not yet
exist? If it's just my stupidity, can someone please give me some
pointers to exactly where I should RTFM?
OK, so why am I attempting this lunacy? I am interested in assisting
with the development of a data store for GIS. Looks like most of the
mooted schemas will involve the creation of large numbers of tables of
identical type. Each of these in turn contains possibly repeating
identical conceptual structures. So to me it made sense, rather than
producing application specific code to manage these, to see if support
could be added to the backend DBMS, so that other applications (e.g.
CAD) could make use of the functionality.
TIA for any assistance.
cheers,
John
--
--
john reid e-mail [EMAIL PROTECTED]
uproot your questions from their ground and the dangling roots will be
seen. more questions!
-mentat zensufi
apply standard disclaimers as desired...
--
[SQL] Re: abstract data types?
Hi Tom, listers, Thanks for the info. > On Sat, 20 Jan 2001, Tom Lane wrote: > > >> None, I fear. The stuff you are fooling with is leftover from the old >> PostQuel language. Most of it is suffering from bit rot, because the >> developers' focus has been on SQL92 compliance for the last six or seven >> years. > Damn! Not what I wanted to hear :-( >> I hadn't realized that SQL99 had caught up to PostQuel in this >> area ;-). > FWIW, this is actually one of the primary reasons that I became interested in PostgreSQL, before I even knew about SQL3/SQL99. Seems like such a cool idea :-) >> Sounds like we will have to dust off some of that stuff and >> get it working again. No promises about timeframe, unless someone >> steps up to the plate to do the work... > OK, what few coding skills I had are so rusty I'm pretty much back to square one, but I would like to help out where possible (Docs maybe?). Then again, might as well jump in the deep end, and have a look to see what needs doing anyway :-) Can you please give me some pointers as to where I should look in the docs and code to see how classes are currently handled. I'm thinking specifically of: * How (and where) the access methods for class tuples are implemented and called. * Where the code for creating classes hides * Anything else that I should be aware of! For the moment I guess I don't need to worry about the parser, just how the operations related to the classes (both system and user) work/are implemented. Correct? > What goes around comes around. :-) And hits you in the back of the head just when you least expect it ... cheers, John -- john reid e-mail [EMAIL PROTECTED] uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
[SQL] Re: abstract data types?
Hi Josh et al, Sorry for the tardy reply, and thanks for your comments. Any suggestions or pointers on robust database design will be greatly appreciated. Josh Berkus wrote: > Jim, > > >>> I'm trying to figure out what support PostgreSQL >> >> offers for SQL99 >> >>> abstract data types. >> > I'm a little curious why what you're attempting couldn't be > done with two columns rather than inventing your own data > type. As somebody who often rescues databases gone bad, > composite data types have not earned a warm place in my > heart ... > > -Josh Berkus What we are attempting is the storage of vector data for a geographical (or spatial) information system in a database. We hope to base the implementation on the upcoming standard from the ISO TC/211 committee. More information can be found at http://FMaps.sourceforge.net/ - the webpages need a major revamp so the best place to look for current developments is in the mailing list archive. A good source of info can be found at http://gdal.velocet.ca/projects/osvecdb/index.html, especially relevant is the comparison of the SQL/MM, OGC, and ISO TC/211 standards (http://gdal.velocet.ca/projects/osvecdb/comp-mm-ogc-tc211.pdf ). To answer your question, it is a bit hard to say at the moment as the design schema for our project has only just been started. The draft versions of the ISO standard that I have seen use an object oriented data model, so to me it makes sense to try and keep the database schema as close as possible to this (minimise data impedance). Briefly, at its' simplest the schema will probably use a two tier approach. Tier 0ne -- The original data stored in the most flexible way that we can think of, with associated metadata tables. Tier Two --- These will effectively be persistent views on the T1 tables structured for efficient access by client applications. OK, as far I know no such beast as a persistent view exists in the SQL standards, but that is probably the best way to describe what I have in mind. Using views as currently implemented in PostgreSQL would probably not be viable as it is likely that, if multiple spatial reference systems are defined on a area of interest, reprojection of the geometry objects would be a performance killer. cheers, John
[SQL] Re: abstract data types?
problems for implementing abstract data types that I can see so far: * Inheritance is currently implemented at the relation level, rather than the type level. Is this simply a matter of changing the references in pg_inherits from pg_class.oid to pg_type.oid? Or would this cause major breakages in other parts of the code? * The existing "CREATE TABLE tablename AS" syntax is incompatible (or needs to be modified to comply) with the SQL99 syntax of "CREATE TABLE tablename AS typename"; * Code for creating a composite attribute member currently implements them as a oid referencing a seperate table. According to Date this is probably "not a Good Thing" (see [DAT00] Section 25.2 pg 865) - in this case relvar = object class rather than his preferred domain = object class. I assume the methods necessary to read and write complex attributes would be similar in nature to those employed for table access - correct? Oh, well. Back to tracing how procedures are called from the system catalogs I guess. From a previous post of mine: "Can you please give me some pointers as to where I should look in the docs and code to see how classes are currently handled. I'm thinking specifically of: * How (and where) the access methods for class tuples are implemented and called. * Where the code for creating classes hides * Anything else that I should be aware of! For the moment I guess I don't need to worry about the parser, just how the operations related to the classes (both system and user) work/are implemented. Correct?" Any help people can give me would be much appreciated. I'm already feeling a little lost. I hope people don't mind if I ask a lot of dumb questions over the next few weeks :-) Is this the appropriate list, or should I move over to hackers? Cheers, John Where I'm getting my info from: Book [Dat00] Author: Date, C.J. Title: An Introduction to Database Systems Publisher: Addison Wesley Longman Date: 2000 Edition: 7th Book [DD00] Author: Date, C.J.; Darwen, Hugh Title: Foundation for Future Database Systems : the Third Manifesto Publisher: Addison Wesley Date: 2000 Edition: 2nd Book [SB99] Author:Stonebraker, Michael; Brown, Paul Title: Object-Relational DBMSs : Tracking the Next Great Wave Publisher: Morgan Kaufmann Date: 1999 Edition: 2nd Book [For99] Author:Fortier, Paul Title: SQL3 Implementing the SQL Foundation Standard Publisher: McGraw Hill Date: 1999 -- john reid e-mail [EMAIL PROTECTED] uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
[SQL] Re: abstract data types?
gt; model -jgr], in the shape of domains (which we prefer to > > call types > > anyway)." > > > > Yeah. Real DOMAIN and TYPE support (which are really two > diffetent things, a Domain being a specification for a more > general Type) in Postgres would be teriffic. Also, IIRC, OO types have methods, domains have only values. I'm not 100% sure on what distinction is made between them in SQL99, whether domains are included in the spec or whether this concept is covered instead by the create distinct type statement. No book to check at the moment :-) > How about it, > Tom, Stephen? Just an idea :-) I'm no cvs guru, but isn't it possible to define seperate branches within the same cvs tree? That way, anyone crazy enough to get involved in this could experiment without running the risk of breaking the MAIN branch, while still keeping up with the latest changes to the code in other areas. Then, when ready, any required changes could be merged back into the main tree. I think some approach similar to this is probably a "Good Thing", as some changes will probably be necessary in the core of the system, resulting in a significant risk of major breakages that we probably don't want to subject other developers to. > > Chapter 1, pg 6). Interesting, I just noticed the > > statement "is truly > > relational (unlike SQL)."! > > Yes -- see my comments above. Market pressues and politics > have caused the ISO to abandon relational standards in > formulating the SQL standard in many areas. > > > Sorry, disagree strongly here. > > Ok. I'm probably just biased, anyway, from being burned by > DB tools claiming both OO and SQL-relational support. > > > As far as I can tell, PostgreSQL has most, if not all, of > > the building > > blocks to supply support for abstract data types already > > in place. > > Whoever thought up the system catalogs (as well) was one > > very smart > > individual. Salutations, whoever you are! > > I'd definitely stand back and applaud any effort to support > this. When I first started with PostgreSQL, I thought it > was a really nifty idea, until I tried to build a database > on it. Puls I soon discovered that nifty ideas do not a > payment-processing database make :-( Naivety is such a wonderful thing. I speak from experience. Now to get bitter, cynical and twisted :-) > > Any help people can give me would be much appreciated. > > I'm already > > feeling a little lost. I hope people don't mind if I ask > > a lot of dumb > > questions over the next few weeks :-) Is this the > > appropriate list, or > > should I move over to hackers? > > You should probably cross-post. This list is the place to > see if a number of other developers are interested in the > functionality you propose (yes), hackers is probably the > place to ask how to make the actual changes. > > I can't help. Heck, I can't even get 7.1 beta to run on an > alternate port. > > -Josh Berkus > > P.S. BTW, John, I'm thrilled to get a discussion of issues, > going here in addition to the how-tos! Cool. However, I'm just not up to the stage of asking the how-to's yet! cheers, John -- -- john reid e-mail [EMAIL PROTECTED] technical officerroom G02, building 41 school of geosciences phone +61 02 4221 3963 university of wollongong fax +61 02 4221 4250 uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
