The following bug has been logged on the website:
Bug reference: 15590
Logged by: Guillaume Outters
Email address: guillaume-postgresql@outters.eu
PostgreSQL version: 9.6.11
Operating system: FreeBSD 10.2
Description:
Encapsulating crosstab_hash in a function (to define its return type) does
not work when the (user-defined) return type owns 'dropped' attributes.
begin transaction;
create type t as (id int, i int, j int);
create function ctt(text, text)
returns setof t
as '$libdir/tablefunc', 'crosstab_hash' language c;
select * from ctt($$ select * from (values (1, 'j', 123), (1, 'i', 0)) t $$,
$$ select * from (values ('i'), ('j')) t $$);
-- -> Works as expected, returns 3 columns: 1 0 123
alter type t add attribute bla text;
alter type t drop attribute bla;
create table tab of t;
select a.* from pg_class c, pg_attribute a where relname in ('t', 'tab') and
c.oid = a.attrelid;
-- -> Type t shows its 4 columns, with bla having been renamed to
'........pg.dropped.4........' an flagged attisdropped
-- -> Table tab was created with 3 columns (correctly ignored t's dropped
column).
select * from ctt($$ select * from (values (1, 'j', 123), (1, 'i', 0)) t $$,
$$ select * from (values ('i'), ('j')) t $$);
-- -> Crashes with:
-- Error: ERROR: invalid return type
-- Détail : Query-specified return tuple has 4 columns but crosstab
returns 3.
-- SQLState: 42601
-- ErrorCode: 0
rollback;