Thread: Composite Types
Hi,
I appreciate any help I can get on this...Recently, I've been experimenting
with the user defined base-types (using CREATE TYPE) with successful results.
But, when it comes to handling classes as composite types, things are not
as straight forward. Here is a case. Suppose I define a composite type (table) called 'bulk':
Table "bulk"
Attribute | Type | Modifier
-----------+---------+----------
id | integer |
ofset | integer |
size | integer |
and use the composite type 'bulk' in another table called 'stuff' as in:
Table "stuff"
Attribute | Type | Modifier
-----------+---------+----------
id | integer |
name | text |
content | bulk |
My intent here is to maintain consistency between the instances of these
tables (i.e., for each instance of 'bulk', there would an equivalent instance
in the 'content' of 'stuff'.
My first question is what is the best way to insert into 'stuff'? I couldn't
come up with a simple solution other than creating the function new_bulk():
CREATE function new_bulk(int4, int4, int4) returns bulk as '
select $1, $2, $3
' LANGUAGE 'sql';
and using it to insert a row in 'stuff':
INSERT into stuff values (1,'test1',(new_bulk(7,8,9)));
INSERT into bulk values (7,8,9);
The 2nd question I have is how to come up with an output function (similar
to output_function in CREATE TYPE) to display 'content' of 'stuff' in
a suitable format? If I type:
select content from stuff;
I get:
content
-----------
136585664
(1 row)
and
select stuff.content.id from content;
gives me:
ERROR: init_fcache: Cache lookup failed for procedure 136585664
Any suggestions/help are appreciated.
Morey Parang
Oak Ridge National Laboratory
parangm@ornl.gov
mjp@ornl.gov writes:
> I appreciate any help I can get on this...Recently, I've been experimenting
> with the user defined base-types (using CREATE TYPE) with successful results.
> But, when it comes to handling classes as composite types, things are not
> as straight forward.
The function-returning-composite-type feature is something we inherited
from Berkeley Postgres ---- it doesn't fit into SQL92 at all, and I
don't think any of the current crop of developers even understand it
very well. It's certainly suffering from bit-rot. The "hobbies"
examples in the regression tests seem to be meant to illustrate how
it was supposed to work, but I don't find them either intelligible or
persuasive.
> If I type:
> select content from stuff;
> I get:
> content
> -----------
> 136585664
> (1 row)
I believe you are looking at a numeric equivalent of a pointer-to-
TupleTableSlot there. Somewhere in the mists of Berkelian prehistory,
there must have been some code that did something useful with that kind
of function result, but I sure as heck can't find much trace of it now.
I have been thinking lately that functions returning tuples might
fit into SQL92 better as table sources. That is, instead of
select foo(bar).whatdoyouwritehere ...
we could write something like
select elementa,elementc+1 from foo(bar)
That doesn't work at the moment, of course, but it's something we
could think about causing to work as part of the querytree redesign
planned for 7.2.
Thoughts anyone?
regards, tom lane
Tom,
Thanks for your feedback and insight. Consider this: Wouldn't it be
fantastically powerful and also type-behavior-consistent if user-defined
composite types (using CREATE TABLE) behave somewhat similar to base types
defined by CREATE TYPE? Again, going back to the earlier example,
the composite type 'bulk':
Table "bulk"
Attribute | Type | Modifier
-----------+---------+----------
id | integer |
ofset | integer |
size | integer |
used in:
Table "stuff"
Attribute | Type | Modifier
-----------+---------+----------
id | integer |
name | text |
content | bulk |
with insertion into 'stuff':
INSERT into stuff values (1,'test1',(new_bulk(7,8,9)));
and using query:
SELECT * from stuff where content.id=7;
would result in something like:
id | name | content.id | content.ofset | content.size
------+--------+------------+---------------+-------------
1 | test1 | 7 | 8 | 9
Although the above doesn't fit into SQL92, it does fit into OO paradigm to
which Postgres is approaching. The point being that, once the class
'bulk' is created and allowed to be used in another class 'stuff', it seems
logical and appropriate that the attributes of bulk be visible and
accessible to 'stuff'. Perhaps a Postgres extension to SQL92?
Regards,
Morey Parang
Oak Ridge National Lab
parangm@ornl.gov
On Wed, Jun 07, 2000 at 12:09:45AM -0400, Tom Lane wrote:
> mjp@ornl.gov writes:
> > I appreciate any help I can get on this...Recently, I've been experimenting
> > with the user defined base-types (using CREATE TYPE) with successful results.
> > But, when it comes to handling classes as composite types, things are not
> > as straight forward.
>
> The function-returning-composite-type feature is something we inherited
> from Berkeley Postgres ---- it doesn't fit into SQL92 at all, and I
> don't think any of the current crop of developers even understand it
> very well. It's certainly suffering from bit-rot. The "hobbies"
> examples in the regression tests seem to be meant to illustrate how
> it was supposed to work, but I don't find them either intelligible or
> persuasive.
>
> > If I type:
> > select content from stuff;
> > I get:
>
> > content
> > -----------
> > 136585664
> > (1 row)
>
> I believe you are looking at a numeric equivalent of a pointer-to-
> TupleTableSlot there. Somewhere in the mists of Berkelian prehistory,
> there must have been some code that did something useful with that kind
> of function result, but I sure as heck can't find much trace of it now.
>
> I have been thinking lately that functions returning tuples might
> fit into SQL92 better as table sources. That is, instead of
> select foo(bar).whatdoyouwritehere ...
> we could write something like
> select elementa,elementc+1 from foo(bar)
>
> That doesn't work at the moment, of course, but it's something we
> could think about causing to work as part of the querytree redesign
> planned for 7.2.
>
> Thoughts anyone?
>
> regards, tom lane
Tom Lane wrote:
> mjp@ornl.gov writes:
> > I appreciate any help I can get on this...Recently, I've been experimenting
> > with the user defined base-types (using CREATE TYPE) with successful results.
> > But, when it comes to handling classes as composite types, things are not
> > as straight forward.
>
> The function-returning-composite-type feature is something we inherited
> from Berkeley Postgres ---- it doesn't fit into SQL92 at all,
Right. And the "doesn't fit into SQL92" is why I would like
to get rid of "composite" and "SET OF composite" values for
functions. We need to get them back in a clearly defined way
for stored procedures, but what's possible now is broken in
concept, and doesn't make much sense to me anyway.
> and I
> don't think any of the current crop of developers even understand it
> very well. It's certainly suffering from bit-rot. The "hobbies"
> examples in the regression tests seem to be meant to illustrate how
> it was supposed to work, but I don't find them either intelligible or
> persuasive.
It was the so called "nested dot" syntax of PG4.2 - which was
never released as nested dot, but was working if someone used
attrname(func-returns-tuple(args))
in the POSTQUEL language! The downside was, that a query
needing more than one attrib from the func, ran it multiple
times, returning separate sets. Thus, JOINing over them in
fact!
I consider this broken from the very first attempt, because
such a function has to go into the rangetable (FROM clause).
> > If I type:
> > select content from stuff;
> > I get:
>
> > content
> > -----------
> > 136585664
> > (1 row)
>
> I believe you are looking at a numeric equivalent of a pointer-to-
> TupleTableSlot there. Somewhere in the mists of Berkelian prehistory,
> there must have been some code that did something useful with that kind
> of function result, but I sure as heck can't find much trace of it now.
Exactly correct. The original POSTQUEL parser had support to
extract a single attribute from a function returning a tuple.
Someone also attempted to add a targetlist to Func nodes.
Both attempts seemed to fail, so we ended up with a "broken
by concept" state.
>
> I have been thinking lately that functions returning tuples might
> fit into SQL92 better as table sources. That is, instead of
> select foo(bar).whatdoyouwritehere ...
> we could write something like
> select elementa,elementc+1 from foo(bar)
>
> That doesn't work at the moment, of course, but it's something we
> could think about causing to work as part of the querytree redesign
> planned for 7.2.
>
> Thoughts anyone?
What I reffered to with "subselecting RTEs" as often as I
could. Seems you finally got me (sorry for my bad english
:-).
If something is broken, fix it. If something is broken by
concept, fix the concept and reimplement the feature. This
time, the concept is broken, so let's first decide how it
should appear on the query level. The FROM foo(bar) above
perfectly fits into the new querytree structure we've
outlined during breakfast in San-Francisco. What a time, we
worked from breakfast until after dinner - it was great.
Anyway, we know that this one would fit into the new concept,
but are we sure anything else would?
For the rewriter, I know that VIEWs will be a nobrainer. But
all other rewriting rules on INSERT/UPDATE/DELETE, turning
views into tables, don't appear to be that easy any more.
Actually we have a central rangetable, and the target is just
"0" (meaning client) or the RTE index of the query. With the
new querytree concept, we'd loose the central rangetable, so
application of modifying rules might not be that simple any
more.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
JanWieck@t-online.de (Jan Wieck) writes:
> Exactly correct. The original POSTQUEL parser had support to
> extract a single attribute from a function returning a tuple.
> Someone also attempted to add a targetlist to Func nodes.
That wasn't there originally, huh? Interesting, because I have been
thinking about ripping it out again. It's essentially useless as it
stands; the only thing any code does with it is to decide whether
an SQL function should return the whole tuple that its final SELECT
produces, or extract a single Datum from the tuple. That could be
done with a simple int field ("extract attr N from tuple").
Since the targetlists attached to Func nodes clutter the parsetree
quite a lot and bulk up stored rule strings, getting rid of them
seems like a good idea.
> For the rewriter, I know that VIEWs will be a nobrainer. But
> all other rewriting rules on INSERT/UPDATE/DELETE, turning
> views into tables, don't appear to be that easy any more.
> Actually we have a central rangetable, and the target is just
> "0" (meaning client) or the RTE index of the query. With the
> new querytree concept, we'd loose the central rangetable, so
> application of modifying rules might not be that simple any
> more.
There'd still be a rangetable for the target of INSERT/UPDATE/DELETE,
I imagine. One thing we should think about while we're in there is
supporting INSERT/UPDATE/DELETE directed to multiple tables (for
inheritance).
regards, tom lane