From: John Reid <jgreid@uow.edu.au>
To: PostgreSQL SQL mailing list <pgsql-sql@postgresql.org>
Subject: abstract data types?
Date: Sun, 21 Jan 2001 16:00:46 +1100
Message-ID: <3A6A6CFE.9000406@uow.edu.au> (raw)
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 john_reid@uow.edu.au
uproot your questions from their ground and the dangling roots will be
seen. more questions!
-mentat zensufi
apply standard disclaimers as desired...
----------------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jgreid@uow.edu.au
Subject: Re: abstract data types?
In-Reply-To: <3A6A6CFE.9000406@uow.edu.au>
References: <3A6A6CFE.9000406@uow.edu.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox