Thread: Re: [QUESTIONS] using composite types
> How can I use composite types?
> If I create two tables like this:
> create table A ( a int, b int);
> create table D ( a A, b int);
> I would expect to be able to
> insert into D (a.a, a.b, b) values (1,2,3);
> and
> select a.a, a.b, b from D;
> That doesn't work, at least not with postgresql 6.3. Is that the way
> it's supposed to work? What is the proper way to do it?
I'm not sure if this is supported. The old tutorial does not have an
example exactly like this (at least that I could find), but it seems
like it _should_ work, and the backend accepts the syntax.
What you want to do can probably be accomplished with inheritance, but
that only seems to work if you have unique names for more of your
fields:
create table A ( a int, b int);
create table D ( c int) inherits (A);
But, if you try
create table D ( b int) inherits (A);
then the new column in D gets lost without warning!
- Tom
> What you want to do can probably be accomplished with inheritance, but
> that only seems to work if you have unique names for more of your
> fields:
>
> create table A ( a int, b int);
> create table D ( c int) inherits (A);
>
> But, if you try
>
> create table D ( b int) inherits (A);
>
> then the new column in D gets lost without warning!
Patient: Ow, doctor, it hurts when I do this.
Doctor: Well, then don't do that.
---------------------------------------------------------------------------
This is a pretty bizarre example, and I am not sure how the system will
handle this. My guess is that it finds only the first b. Seems like we
need a check somewhere when making inheritance. I will add it to the
TODO.
* disallow inherited columns with the same name as new columns
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
On Fri, 13 Mar 1998, Thomas G. Lockhart wrote: > > How can I use composite types? > > If I create two tables like this: > > create table A ( a int, b int); > > create table D ( a A, b int); > > I would expect to be able to > > insert into D (a.a, a.b, b) values (1,2,3); > > and > > select a.a, a.b, b from D; > > That doesn't work, at least not with postgresql 6.3. Is that the way > > it's supposed to work? What is the proper way to do it? I've just tried it here, and it seems that the value to insert into D is the OID of a row in table A. > I'm not sure if this is supported. The old tutorial does not have an > example exactly like this (at least that I could find), but it seems > like it _should_ work, and the backend accepts the syntax. > > What you want to do can probably be accomplished with inheritance, but > that only seems to work if you have unique names for more of your > fields: > > create table A ( a int, b int); > create table D ( c int) inherits (A); > > But, if you try > > create table D ( b int) inherits (A); > > then the new column in D gets lost without warning! This would give a table based on the parent table, but I think he was trying to use table D to refer to values in table A _as_ a type. [I may be wrong here, but that's what it looks like] -- Peter T Mount petermount@earthling.net or pmount@maidast.demon.co.uk Main Homepage: http://www.demon.co.uk/finder Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
> On Fri, 13 Mar 1998, Thomas G. Lockhart wrote: > > > > How can I use composite types? > > > If I create two tables like this: > > > create table A ( a int, b int); > > > create table D ( a A, b int); > > > I would expect to be able to > > > insert into D (a.a, a.b, b) values (1,2,3); > > > and > > > select a.a, a.b, b from D; > > > That doesn't work, at least not with postgresql 6.3. Is that the way > > > it's supposed to work? What is the proper way to do it? > > I've just tried it here, and it seems that the value to insert into D is > the OID of a row in table A. > > > I'm not sure if this is supported. The old tutorial does not have an > > example exactly like this (at least that I could find), but it seems > > like it _should_ work, and the backend accepts the syntax. > > > > What you want to do can probably be accomplished with inheritance, but > > that only seems to work if you have unique names for more of your > > fields: > > > > create table A ( a int, b int); > > create table D ( c int) inherits (A); > > > But, if you try > > > > create table D ( b int) inherits (A); > > > > then the new column in D gets lost without warning! > This would give a table based on the parent table, but I think he was > trying to use table D to refer to values in table A _as_ a type. Yes, I am trying to use A as a type. I was hoping there was some syntax sugar that I was missing; the C-like struct traversal syntax would have been nice, but oh well. I see how an oid could work, but the idea was to make my life as a programmer easier. I'll just prepend the A_ to a and b in A and inherit, so I can "select A_a, A_b, b from D". I got lucky and all my prefixed names fit in under the 32 character name limit. Thanks, Brian