Выполнить динамический запрос кросс-таблицы
Я реализовал эту функцию в моей базе данных Postgres:http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
Вот функция:
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
$;
Итак, теперь я могу вызвать функцию:
select xtab('globalpayments','month','currency','(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2)','text');
Который возвращает (потому что возвращаемый тип функции 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 );
Как я могу получить эту функцию, чтобы не только генерировать код для динамической кросс-таблицы, но и выполнять результат? Т.е. результат, когда я вручную копирую / вставляю / выполняю, таков. Но я хочу, чтобы он выполнялся без этого дополнительного шага: функция должна собрать динамический запроса также выполнить это:
Редактировать 1
Эта функция близка, но мне нужно, чтобы она возвращала не только первый столбец первой записи
Взято из:Есть ли способ выполнить запрос внутри строкового значения (например, eval) в 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
использование:select * from eval($$select * from analytics limit 1$$)
Однако он просто возвращает первый столбец первой записи:
eval
----
2015
когда фактический результат выглядит так:
Year, Month, Date, TPV_USD
---- ----- ------ --------
2016, 3, 2016-03-31, 100000