Thread: composite type insert

I'm trying to understand how to insert a composite type value.  I'm
having trouble with the last sql statement below.  It errors out with:

ERROR:  cannot cast type record to atype

How should I create a composite type value out of columns a and b in
table tt that I can insert into table atable?

CREATE TYPE atype AS (
  acol
    TEXT,
  bcol
    TEXT
);

CREATE TABLE atable (
  aval
    atype
);

CREATE TEMP TABLE
  tt
AS
  SELECT 'aaa'::text AS a, 'bbb'::text AS b;

INSERT INTO
  atable
SELECT
  ROW(a, b)
FROM
  tt;

Thanks as always.

--
Ron Peterson
https://www.yellowbank.com/

Ron Peterson <ron.peterson@yellowbank.com> writes:
> How should I create a composite type value out of columns a and b in
> table tt that I can insert into table atable?

Hm, it works for me with an explicit cast:

INSERT INTO
  atable
SELECT
  ROW(a, b)::atype
FROM
  tt;

Perhaps we should allow this in an automatic or even implicit cast
context.

            regards, tom lane

On Sun, Nov 19, 2006 at 02:09:11AM -0500, Tom Lane wrote:
> Ron Peterson <ron.peterson@yellowbank.com> writes:
> > How should I create a composite type value out of columns a and b in
> > table tt that I can insert into table atable?
>
> Hm, it works for me with an explicit cast:
>
> INSERT INTO
>   atable
> SELECT
>   ROW(a, b)::atype
> FROM
>   tt;
>
> Perhaps we should allow this in an automatic or even implicit cast
> context.

Ah, I can certainly live with a cast.  I didn't realize that creating a
composite type with 'create type' also automatically created a cast.  I
should have at least tried that.  Maybe section 8.11 (Composite Types)
or the 'CREATE TYPE' section of the docs should mention this?

Best.

--
Ron Peterson
https://www.yellowbank.com/