Обсуждение: INSERT with a composite columnt from query

Hi all.

I have two table like these:

create table compo (
  t text,
  i int
);

create table tab (
  x int,
  c compo
);

Then I have a function like this:

create or replace function f_compo()
returns setof compo as $body$
...
$body$ language sql stable;


What I'd need to do is to insert the results from f_compo() into
the table TAB along with a value x.

I expected somthing like this to work:

insert into tab
  select 42,row( c.* ) from f_compo() c;

But I get
ERROR:  cannot cast type record to compo

Any hint?

TALIA

--
Reg me, please!

Reg Me Please wrote:
> I have two table like these:
> 
> create table compo (
>   t text,
>   i int
> );
> 
> create table tab (
>   x int,
>   c compo
> );
> 
> Then I have a function like this:
> 
> create or replace function f_compo()
> returns setof compo as $body$
> ...
> $body$ language sql stable;
> 
> 
> What I'd need to do is to insert the results from f_compo() into
> the table TAB along with a value x.
> 
> I expected somthing like this to work:
> 
> insert into tab
>   select 42,row( c.* ) from f_compo() c;
> 
> But I get
> ERROR:  cannot cast type record to compo

The whole exercise seems a bit pointless, but you could do it like this:

INSERT INTO tab SELECT 42, CAST (c AS compo) FROM f_compo() c;

Yours,
Laurenz Albe

Reg Me Please wrote:
>
> What I'd need to do is to insert the results from f_compo() into
> the table TAB along with a value x.
>
> I expected somthing like this to work:
>
> insert into tab
>   select 42,row( c.* ) from f_compo() c;
>
> But I get
> ERROR:  cannot cast type record to compo

You need to add an explicit cast I believe.

INSERT INTO tab (x,c)
SELECT 42, ROW(c.*)::compo FROM f_compo() c;

Why you don't in the case of INSERT ... VALUES isn't immediately clear
to me.

--
   Richard Huxton
   Archonet Ltd