postgresql - Ejecutar una consulta de tabla cruzada dinámica

plpgsql dynamic-sql

Question

Implementé esta función en mi base de datos Postgres: http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

Aquí está la función:

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
$$;

Entonces ahora puedo llamar a la función:

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

Que devuelve (porque el tipo de retorno de la función es 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 );

¿Cómo puedo hacer que esta función no solo genere el código para la tabla cruzada dinámica, sino que también ejecute el resultado? Es decir, el resultado cuando copio / pego / ejecuto manualmente es este. Pero quiero que se ejecute sin ese paso adicional: la función ensamblará la consulta dinámica y la ejecutará:

Editar 1

Esta función se acerca, pero necesito que devuelva más que solo la primera columna del primer registro

Tomado de: ¿Hay alguna forma de ejecutar una consulta dentro del valor de cadena (como eval) en 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$$)

Sin embargo, solo devuelve la primera columna del primer registro:

eval
----
2015

cuando el resultado real se ve así:

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

Answer 1

Lo que pides es imposible . SQL es un lenguaje estrictamente escrito. Las funciones de PostgreSQL deben declarar un tipo de retorno ( RETURNS .. ) en el momento de la creación .

Una forma limitada de evitar esto es con funciones polimórficas. Si puede proporcionar el tipo de retorno en el momento de la llamada a la función . Pero eso no es evidente por su pregunta.

• Refactorice una función PL / pgSQL para devolver la salida de varias consultas SELECT

Puede devolver un resultado completamente dinámico con registros anónimos. Pero luego debe proporcionar una lista de definición de columna con cada llamada. ¿Y cómo sabes sobre las columnas devueltas? 22 capturas.

Existen varias soluciones alternativas, según lo que necesite o con lo que pueda trabajar. Dado que todas sus columnas de datos parecen compartir el mismo tipo de datos, sugiero que devuelva una matriz : text[] . O puede devolver un tipo de documento como hstore o json . Relacionado:

• Alternativa dinámica al pivote con CASE y GROUP BY

• Convierta dinámicamente las claves de hstore en columnas para un conjunto desconocido de claves

Pero podría ser más simple usar solo dos llamadas: 1: Deje que Postgres construya la consulta. 2: Ejecutar y recuperar filas devueltas.

• Seleccionar múltiples valores max () usando una sola instrucción SQL

No usaría la función de Eric Minikel como se presenta en su pregunta. No es seguro contra la inyección de SQL por medio de identificadores mal formados maliciosamente. Use format() para crear cadenas de consulta a menos que esté ejecutando una versión desactualizada anterior a Postgres 9.1.

Una implementación más corta y limpia podría verse así:

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;

La misma llamada de función que su versión original. La función crosstab() tablefunc crosstab() es proporcionada por el módulo adicional tablefunc que debe instalarse. Lo esencial:

• PostgreSQL Crosstab Query

Esto maneja los nombres de columnas y tablas de forma segura. Tenga en cuenta el uso de los tipos de identificador de objeto regclass y regtype . También funciona para nombres con calificación de esquema.

• Nombre de tabla como parámetro de función PostgreSQL

Sin embargo, no es completamente seguro mientras pasa una cadena para que se ejecute como expresión ( _expr - cellc en su consulta original). Este tipo de entrada es inherentemente inseguro contra la inyección SQL y nunca debe exponerse al público en general.

• Inyección SQL en funciones de Postgres vs consultas preparadas

Escanea la tabla solo una vez para ambas listas de categorías y debería ser un poco más rápido.

Todavía no se pueden devolver tipos de fila completamente dinámicos, ya que eso es estrictamente imposible.

Answer 2

No es imposible, aún puede ejecutarlo (desde una consulta, ejecute la cadena y devuelva SETOF RECORD.

Luego debe especificar el formato de registro de retorno. La razón en este caso es que el planificador necesita conocer el formato de devolución antes de poder tomar ciertas decisiones (me viene a la mente la materialización).

Entonces, en este caso, EJECUTARÍA la consulta, devolvería las filas y devolvería SETOF RECORD.

Por ejemplo, podríamos hacer algo como esto con una función de contenedor, pero la misma lógica podría incorporarse a su función:

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;
 $$;

Luego, proporciona la estructura de registro al llamar a la función tal como lo hace con la tabla de referencias cruzadas. Luego, cuando realice todas las consultas, deberá proporcionar una estructura de registro (como (tipo col1, tipo col2, etc.) como lo hace con connectby.