Executar uma consulta de tabela de referência cruzada dinâmica

Implementei esta function na minha base de dados do Postgres: http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

Aqui está a function:

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 function:

 select xtab('globalpayments','month','currency','(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2)','text'); 

Que retorna (porque o tipo de retorno da function é 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 function não apenas para gerar o código para a crosstab dinâmica, mas também para executar o resultado? Ou seja, o resultado quando copio / colo / executo manualmente é isso. Mas eu quero que ele execute sem esse passo extra: a function deve montar a consulta dinâmica e executá-la:

resultado da consulta

Editar 1

Esta function chega perto, mas eu preciso retornar mais do que apenas a primeira coluna do primeiro registro

Retirado 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 se parece com isso:

 Year, Month, Date, TPV_USD ---- ----- ------ -------- 2016, 3, 2016-03-31, 100000 

O que você pede é impossível . SQL é uma linguagem rigidamente tipada. As funções do PostgreSQL precisam declarar um tipo de retorno ( RETURNS .. ) no momento da criação .

Uma maneira limitada em torno disso é com funções polimórficas. Se você puder fornecer o tipo de retorno no momento da chamada de function . Mas isso não é evidente a partir da sua pergunta.

  • Refatore uma function PL / pgSQL para retornar a saída de várias consultas SELECT

Você pode retornar um resultado completamente dynamic com registros anônimos. Mas então você é obrigado a fornecer uma lista de definição de coluna a cada chamada. E como você sabe sobre as colunas retornadas? Captura 22

Existem várias soluções alternativas, dependendo do que você precisa ou pode trabalhar. Como todas as colunas de dados parecem compartilhar o mesmo tipo de dados, sugiro que você retorne uma matriz : text[] . Ou você pode retornar um tipo de documento como hstore ou json . Relacionado:

  • Alternativa dinâmica para dinamizar com CASE e GROUP BY

  • Converter dinamicamente chaves hstore em colunas para um conjunto desconhecido de chaves

Mas pode ser mais simples usar apenas duas chamadas: 1: Deixe o Postgres construir a consulta. 2: Execute e recupere as linhas retornadas.

  • Selecionando vários valores max () usando uma única instrução SQL

Eu não usaria a function de Eric Minikel como apresentado na sua pergunta em tudo . Não é seguro contra a injeção de SQL por meio de identificadores mal-formados maliciosos. Use format() para construir strings de consulta, a menos que você esteja executando uma versão desatualizada mais antiga que o Postgres 9.1.

Uma implementação mais curta e mais limpa poderia ser assim:

 CREATE OR REPLACE FUNCTION xtab(_tbl regclass, _row text, _cat text , _expr text -- still vulnerable to SQL injection! , _type regtype) RETURNS text AS $func$ DECLARE _cat_list text; _col_list text; BEGIN -- generate categories for xtab param and col definition list EXECUTE format( $$SELECT string_agg(quote_literal(x.cat), '), (') , string_agg(quote_ident (x.cat), %L) FROM (SELECT DISTINCT %I AS cat FROM %s ORDER BY 1) x$$ , ' ' || _type || ', ', _cat, _tbl) INTO _cat_list, _col_list; -- generate query string RETURN format( 'SELECT * FROM crosstab( $q$SELECT %I, %I, %s FROM %I GROUP BY 1, 2 -- only works if the 3rd column is an aggregate expression ORDER BY 1, 2$q$ , $c$VALUES (%5$s)$c$ ) ct(%1$I text, %6$s %7$s)' , _row, _cat, _expr -- expr must be an aggregate expression! , _tbl, _cat_list, _col_list, _type ); END $func$ LANGUAGE plpgsql; 

Mesma chamada de function que a sua versão original. A function crosstab() é fornecida pelo tablefunc módulo adicional que deve ser instalado. Noções básicas:

  • Consulta de referência cruzada do PostgreSQL

Isso lida com nomes de colunas e tabelas com segurança. Observe o uso dos tipos de identificador de object regclass e regtype . Também funciona para nomes qualificados pelo esquema.

  • Nome da tabela como um parâmetro de function do PostgreSQL

No entanto, não é completamente seguro enquanto você passa uma string para ser executada como expressão ( _exprcellc em sua consulta original). Esse tipo de input é intrinsecamente inseguro contra a injeção de SQL e nunca deve ser exposto ao público em geral.

  • Injeção de SQL em funções do Postgres vs consultas preparadas

Escaneia a tabela apenas uma vez para as duas listas de categorias e deve ser um pouco mais rápido.

Ainda não é possível retornar completamente os tipos de linhas dinâmicas, já que isso não é possível.

Não é totalmente impossível, você ainda pode executá-lo (a partir de uma consulta, execute a string e retorne SETOF RECORD.

Então você tem que especificar o formato de registro de retorno. A razão, nesse caso, é que o planejador precisa conhecer o formato de retorno antes de tomar certas decisões (a materialização vem à mente).

Portanto, nesse caso, você executaria a consulta, retornaria as linhas e retornaria SETOF RECORD.

Por exemplo, poderíamos fazer algo assim com uma function de wrapper, mas a mesma lógica poderia ser dobrada em sua function:

 CREATE OR REPLACE FUNCTION crosstab_wrapper (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns setof record language plpgsql as $$ DECLARE outrow record; BEGIN FOR outrow IN EXECUTE xtab($1, $2, $3, $4, $5) LOOP RETURN NEXT outrow END LOOP; END; $$; 

Em seguida, você fornece a estrutura de registro ao chamar a function da mesma forma que faz com a tabela de referência cruzada. Então, quando você fizer toda a consulta, você terá que fornecer uma estrutura de registro (como (tipo col1, tipo col2, etc) como você faz com o connectby.

    Intereting Posts