st 6. 10. 2021 v 5:50 odesílatel <ml@ft-c.de> napsal:
Hello,
there is a bug with types in functions. Here is an example:
--drop type xyz; create type xyz as ( x numeric, y numeric, z numeric );
--drop table test_xyz ; create table test_xyz ( a int, b xyz, c xyz ); insert into test_xyz values ( 1,(11.1, 12.3, 14.5 ), (22.2, 22.3, 24.5 ) ) ;
--drop function test_xyz() ; create function test_xyz() returns table ( a xyz, b xyz) as $$ select b, c from test_xyz ; $$ language sql ;
--drop function test_xyz2() ; create function test_xyz2() returns table ( b xyz) as $$ select b from test_xyz ; $$ language sql ;
--drop function test_xyz3() ; create function test_xyz3() returns table ( b xyz) as $$ declare bb xyz; cc xyz; begin select b, c into bb, cc from test_xyz ; return bb ; end; $$ language plpgsql ; -- ------ select * from test_xyz() ; select * from test_xyz2() ; -- 3 columns, but 1 should be correct? select b from test_xyz2() ; -- error select x from test_xyz2() ; -- never declared column select * from test_xyz3() ; -- compiling error
This is correct;
This should be:
create function test_xyz3() returns table ( b xyz) as $$ declare bb xyz; cc xyz;
r record
begin
-- this case is not supported, you cannot to assign to list of composite variables
-- select b, c into bb, cc from test_xyz ;
select b, c into r;
bb := r.b;
-- you cannot to use return bb, because expected result type is (xyz) not xyz
return next; end; $$ language plpgsql ;
I am not sure, if you want to use RETURNS table(), because in this case multi row result is expected. In this case you should to use RETURN NEXT instead RETURN in PLpgSQL