Last active
January 23, 2019 20:50
-
-
Save adunstan/818490 to your computer and use it in GitHub Desktop.
generate a select query for a given table, with optional alias and compaction
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_select_query (tablename text, tablealias text default null, compact boolean default false) | |
returns text | |
language plpgsql as | |
$$ | |
declare | |
rec record; | |
crec record; | |
firstrow boolean := true; | |
result text := ''; | |
talias text := ''; | |
prefix text := ''; | |
len int; | |
lastwaslf boolean := false; | |
begin | |
if coalesce(tablealias,'') <> '' then | |
talias := quote_ident(tablealias); | |
prefix := talias || '.'; | |
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 := E'select\n '; | |
else | |
result := result || | |
case | |
when lastwaslf then ',' | |
when compact then ', ' | |
else ' ,' | |
end; | |
end if; | |
result := result || prefix || rec.attname; | |
lastwaslf := false; | |
if not compact | |
then | |
result := result || ' -- ' || rec.attype || E' \n'; | |
else | |
len := strpos(reverse(result),E'\n'); | |
if len = 0 | |
then | |
len := length(result); | |
end if; | |
if len > 60 | |
then | |
result := result || E'\n '; | |
lastwaslf := true; | |
end if; | |
end if; | |
firstrow := false; | |
end loop; | |
if compact | |
then | |
result := result || E'\n'; | |
end if; | |
result := result || 'from ' || crec.nspname || | |
'.' || crec.relname; | |
if talias <> '' then | |
result := result || ' as ' || talias; | |
end if; | |
result := result || E'\n'; | |
return result; | |
end; | |
$$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment