Created
February 9, 2011 13:47
-
-
Save adunstan/818496 to your computer and use it in GitHub Desktop.
make a type list for a given type for use in with things like set returning functions, with optional alias name
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
$$; | |
yes, it is. I did it like this so I had more control over formatting.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I think this may be doable as an SQL function using string_agg() or similar :)