error caused by FOREIGN KEY on composite type

[silly8888]

I have the following definitions:

--------------------------------------

create type mytype as (x integer, y integer);

create table foo(
    a mytype primary key,
    b integer
);

create table bar(
    a mytype references foo
);

insert into foo values((0,0)::mytype,0);

--------------------------------------

When I try to do a simple update on foo, I get an error:

test=> update foo set b=1;
ERROR:  no conversion function from mytype to record

Can someone explain what does this error mean?

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Tom Lane-2]

silly8888 <silly8888@...> writes:
> create type mytype as (x integer, y integer);

> create table foo(
>     a mytype primary key,
>     b integer
> );

> create table bar(
>     a mytype references foo
> );

While that probably ought to work, is there a really good reason that
you're not doing this with a conventional two-column primary key and
foreign key?  The composite type is going to be exceedingly inefficient,
not to mention not portable to other DBMSes.

                        regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[silly8888]

On Wed, Nov 4, 2009 at 11:23 PM, Tom Lane <tgl@...> wrote:

> silly8888 <silly8888@...> writes:
>> create type mytype as (x integer, y integer);
>
>> create table foo(
>>     a mytype primary key,
>>     b integer
>> );
>
>> create table bar(
>>     a mytype references foo
>> );
>
> While that probably ought to work, is there a really good reason that
> you're not doing this with a conventional two-column primary key and
> foreign key?  The composite type is going to be exceedingly inefficient,
> not to mention not portable to other DBMSes.
>
>                        regards, tom lane
>

You are right, the two-column solution is probably better. The only
reason I posted here was to see if I hit a bug (and it seems that I
might have).

BTW the composite might offer some small benefits in this case, namely
when combined with user defined DOMAINs it can simplify CHECK
constraints a lot.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general