Re: type bug?
st 6. 10. 2021 v 5:50 odesílatel 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 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 > > > >
Re: type bug?
On Tuesday, October 5, 2021, 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.
Re: type bug?
On Tuesday, October 5, 2021, 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: https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS 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.
Re: type bug?
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, m...@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 > > > > > > >
Re: type bug?
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, m...@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.
