Обсуждение: type bug?
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 -- ------ (I am not a member of the pg-developer mailinglist. ) Franz
What version of Postgresql do you think has the bug? Where did you get the Postgresql binaries from? What platform? On 10/5/21 10:50 PM, ml@ft-c.de wrote: > 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 > -- ------ > > (I am not a member of the pg-developer mailinglist. ) > > Franz > > > -- Angular momentum makes the world go 'round.
On Wed, 2021-10-06 at 00:21 -0500, Ron wrote: > > What version of Postgresql do you think has the bug? > Where did you get the Postgresql binaries from? > What platform? FreeBSD pkg install postgresql ... Version 13.3 a friend use Debian unstable with PG version 14 and have the same problem. > > On 10/5/21 10:50 PM, ml@ft-c.de wrote: > > 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 > > -- ------ > > > > (I am not a member of the pg-developer mailinglist. ) > > > > Franz > > > > > > >
On Tuesday, October 5, 2021, <ml@ft-c.de> wrote:
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
All three of these stem from:
If the function is defined to return a composite type, the table function produces a column for each attribute of the composite type.
Table(composite) == “setof composite”. (Though it seems to not be explicitly documented in that way, it explains the observed behavior)
David J.
On Tuesday, October 5, 2021, <ml@ft-c.de> wrote:
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_xyz3() ; -- compiling error
Help others help you by writing out exact error messages (many of us can answer from just reading code and error messages without needing be able to execute said code ourselves). I suspect “table test_xyz not found” due to the typo.
David J.
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;
$$
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 ;
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
https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
Regards
Pavel
-- ------
(I am not a member of the pg-developer mailinglist. )
Franz