Thread: 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