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

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.

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;
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

-- ------