Re: how to insert values into complex type field

On Fri, 4 Apr 2008 windwxc(at)sina(dot)com wrote:

> hi all, i want to know how to insert values into the field which is a complex type. In fact it is a complex type which also include a complex type. The following is its definition:
> create TYPE lifetime as( strattime date, endtime date);

> create TYPE attributetype as( ID numeric, address character(50),
> periodspan lifetime);

> create TABLE attribute2005( gid serial, allfield attributetype);
> now i want to insert data into the table attribute2005 but always failure so wish someone can help.
> my sql is following:

> INSERT INTO attribute2005
> VALUES(1,(23,'ee','ttt',('2005-01-01','2005-12-31')));

I'm running on 8.3, but in that version at least, it looks like you have
a few options for the values and one of these should hopefully work in
8.2.

VALUES (1, ROW(23, 'ee', ROW('2005-01-01', '2005-12-31')));
VALUES (1, '(23,"ee","(2005-01-01,2005-12-31)")')
There are other slight variations on this second one, you can remove the
double quotes around ee and it looks like you can add double quotes around
the dates, etc.

It also looks like your attibute type above only had 2 scalars and the
complex type rather than three, so I've dropped the 'ttt' for the examples
above.