make a type list for a given type for use in with things like set returning functions, with optional alias name

make_type_list.sql

create or replace function make_type_list(tablename text, typealias text default null) returns text
language plpgsql as

$$

declare 
        rec record;
        crec record;
        firstrow boolean := true;
        talias text := '';
        result text := '';
begin
        if coalesce(typealias,'') <> '' then
           talias := quote_ident(typealias);
        end if;

        select into crec c.oid, quote_ident(nspname) as nspname, quote_ident(relname) as relname
        from pg_class c, pg_namespace n
        where c.relnamespace = n.oid
        and c.oid = tablename::regclass;

        for rec in
            select quote_ident(attname) as attname, format_type(atttypid,atttypmod) as attype
            from pg_attribute 
            where attrelid = crec.oid
                  and attnum > 0
                  and not attisdropped
            order by attnum
        loop
            if firstrow then
               result := talias || '(';
               firstrow := false;
            else    
               result := result || '    ,';
            end if;
            result := result || rec.attname || '     ' || rec.attype ||  E' \n';
        end loop;
        result := result || E')\n';
        return result;
end;

$$;

I think this may be doable as an SQL function using string_agg() or similar :)

yes, it is. I did it like this so I had more control over formatting.