Executar uma consulta dinâmica de crosstab
Eu implementei essa função no meu banco de dados Postgres:http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
Aqui está a função:
create or replace function xtab (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns varchar language plpgsql as $
declare
dynsql1 varchar;
dynsql2 varchar;
columnlist varchar;
begin
-- 1. retrieve list of column names.
dynsql1 = 'select string_agg(distinct '||colc||'||'' '||celldatatype||''','','' order by '||colc||'||'' '||celldatatype||''') from '||tablename||';';
execute dynsql1 into columnlist;
-- 2. set up the crosstab query
dynsql2 = 'select * from crosstab (
''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'',
''select distinct '||colc||' from '||tablename||' order by 1''
)
as ct (
'||rowc||' varchar,'||columnlist||'
);';
return dynsql2;
end
$;
Então agora eu posso chamar a função:
select xtab('globalpayments','month','currency','(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2)','text');
Que retorna (porque o tipo de retorno da função é varchar):
select * from crosstab (
'select month,currency,(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2)
from globalpayments
group by 1,2
order by 1,2'
, 'select distinct currency
from globalpayments
order by 1'
) as ct ( month varchar,CAD text,EUR text,GBP text,USD text );
Como posso obter essa função não apenas para gerar o código da crosstab dinâmica, mas também para executar o resultado? Ou seja, o resultado quando eu copio / colo / executo manualmente é esse. Mas eu quero que ele seja executado sem essa etapa extra: a função deve montar a consulta dinâmicae execute:
Editar 1
Essa função se aproxima, mas preciso que ela retorne mais do que apenas a primeira coluna do primeiro registro
Tirado de:Existe alguma maneira de executar uma consulta dentro do valor da string (como eval) no PostgreSQL?
create or replace function eval( sql text ) returns text as $
declare
as_txt text;
begin
if sql is null then return null ; end if ;
execute sql into as_txt ;
return as_txt ;
end;
$ language plpgsql
uso:select * from eval($$select * from analytics limit 1$$)
No entanto, apenas retorna a primeira coluna do primeiro registro:
eval
----
2015
quando o resultado real estiver assim:
Year, Month, Date, TPV_USD
---- ----- ------ --------
2016, 3, 2016-03-31, 100000