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