Thread: Composite Types

Composite Types

From
mjp@ornl.gov
Date:
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

Re: Composite Types

From
Tom Lane
Date:
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

Re: Composite Types

From
mjp@ornl.gov
Date:
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

Re: Composite Types

From
JanWieck@t-online.de (Jan Wieck)
Date:
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 #



Re: Composite Types

From
Tom Lane
Date:
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