Enable Testing for Function With Return Type TABLE (id INTEGER, name TEXT)

[dianhenglau]

Please refer to the topic in mailing list.

[theory]

To be taken on once support for 8.4 is dropped. Might have to tweak things, though, to support both setof record and table, if it would make existing tests fail.

[wphilips53]

In hindsight, something like this may be more useful given the way tap_funky is setup. I imagine you could easily adjust function returns/parameter checks to use something like this.

CREATE OR REPLACE FUNCTION public._unnest_args(_arg_names _text, _arg_types _oid, _io_types _char)
 returns 
 	table(table_args varchar[], table_types name[], 
 		  input_args varchar[], input_types name[],
 		  inout_args varchar[], inout_types name[],
 		  variadic_args varchar[], variadic_types name[],
 		  out_args varchar[], out_types name[])
 stable
 LANGUAGE plpgsql
AS $function$
begin
	return query
		select 
			array_agg(_table_arg) filter(where _table_arg is not null) as _table_args,
			array_agg(_table_type) filter(where _table_type is not null) as _table_types,
			array_agg(_input_arg) filter(where _input_arg is not null) as _input_args,
			array_agg(_input_type) filter(where _input_type is not null) as _input_types,
			array_agg(_inout_arg) filter(where _inout_arg is not null) as _inout_args,
			array_agg(_inout_type) filter(where _inout_type is not null) as _inout_types,
			array_agg(_variadic_arg) filter(where _variadic_arg is not null) as _variadic_args,
			array_agg(_variadic_type) filter(where _variadic_type is not null) as _variadic_types,
			array_agg(_out_arg) filter(where _out_arg is not null) as _out_args,
			array_agg(_out_type) filter(where _out_type is not null) as _out_types
		from (
			select 	
				(case when io_type = 't' then arg_name else null end) as _table_arg,
				(case when io_type = 't' then p.typname else null end) as _table_type,
				(case when io_type = 'i' then arg_name else null end) as _input_arg,
				(case when io_type = 'i' then p.typname else null end) as _input_type,
				(case when io_type = 'b' then arg_name else null end) as _inout_arg,
				(case when io_type = 'b' then p.typname else null end) as _inout_type,
				(case when io_type = 'v' then arg_name else null end) as _variadic_arg,
				(case when io_type = 'v' then p.typname else null end) as _variadic_type,
				(case when io_type = 'o' then arg_name else null end) as _out_arg,
				(case when io_type = 'o' then p.typname else null end) as _out_type
			from (
				select unnest(_arg_names)::varchar as arg_name, unnest(_arg_types)::oid as arg_type, unnest(_io_types)::char as io_type
			) t1
			inner join pg_catalog.pg_type p
			on t1.arg_type = p."oid" 
		) t2;
end;
$function$;
 ---------------------------------------------------------------------------------------------------------------
select p.proargnames as names, p.proallargtypes as typesof, p.proargmodes, t.*
from pg_proc p, public._unnest_args(p.proargnames,  p.proallargtypes, p.proargmodes)  t;