Bases de Datos - consulta postgres

CREATE OR REPLACE FUNCTION __data_warehouse.sp_genera_cubos_multidimensionales(

    ifilas character varying,

    icolumnas character varying,

    ibancolumnas character varying,

    ivalores character varying,

    itabla_nomb character varying,

    itabla_salida character varying,

    ifiltros character varying)

  RETURNS void AS

$BODY$

declare	vquery text;

declare	vquery_cabecera text;

declare	vnum_filas integer;

declare	vnum_columnas integer;

declare	vcolumnas_salida text;

declare	vfilas_salida text;

declare	vconsulta_salida text;

declare	vconsulta_cabecera text;

declare vconsulta_cabecera_sum text;

declare vconsulta_cabecera_sum_total text;

declare	vconsulta_pie text;

declare	vsum_total text;

declare	vaux_filas text;

declare	vacum text;

declare	i integer;

declare	j integer;

declare	k integer;

declare	l integer;

declare	ban integer;

declare vcant_num_reg integer;

declare	vacum_num_reg text;

declare v_cursor_cab refcursor;

declare	Icab_corr text;

declare	Icab_sequencia text;

BEGIN

--*calculo del numero de filas

vnum_filas:=(select char_length(trim(ifilas)))-(select char_length(trim(replace(ifilas,',',''))))+1;

--*calculo del numero de columnas

if  split_part(icolumnas, ',', 2)<>'' then

vquery:='select count(*) as num_reg

from (select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||') a';

EXECUTE vquery

into vnum_columnas;

--****GENERAMOS CABECERAS CON NOMBRE CORRECTO****

if cast(ibancolumnas as boolean)=cast('t' as boolean) then

vquery:='drop table if exists aux_cabecera;

create temporary table aux_cabecera as

select '||icolumnas||',substring(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace('||split_part(icolumnas, ',', 1)||','' '',''''),''1'',''''),''2'',''''),''3'',''''),''4'',''''),''5'',''''),''6'',''''),''7'',''''),''8'',''''),''9'',''''),''0'',''''),''-'',''''),1,4)

||substring(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace('||split_part(icolumnas, ',', 2)||','' '',''''),''1'',''''),''2'',''''),''3'',''''),''4'',''''),''5'',''''),''6'',''''),''7'',''''),''8'',''''),''9'',''''),''0'',''''),''-'',''''),1,4)

||cast((row_number() over (ORDER BY 1)) as varchar) as cab_corr,''cols''||cast((row_number() over (ORDER BY 1)) as varchar) as cab_sequencia,row_number() over (ORDER BY 1) as orden

from (

select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||'

order by '||icolumnas||')a;';

EXECUTE vquery;

else

vquery:='drop table if exists aux_cabecera;

create temporary table aux_cabecera as

select '||icolumnas||',''_''||substring(replace('||split_part(icolumnas, ',', 1)||',''-'',''''),1,4)

||substring(replace('||split_part(icolumnas, ',', 2)||',''-'',''''),1,4)

||cast((row_number() over (ORDER BY 1)) as varchar) as cab_corr,''cols''||cast((row_number() over (ORDER BY 1)) as varchar) as cab_sequencia,row_number() over (ORDER BY 1) as orden

from (

select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||'

order by '||icolumnas||')a;';

EXECUTE vquery;

end if;

else

vquery:='select sum(num_col) as total_col

from (

select '||split_part(icolumnas,',',1)||',count(*) as num_col

from (

select '||icolumnas||'

from '||itabla_nomb||'

 group by '||icolumnas||'

 order by '||icolumnas||') a

group by '||split_part(icolumnas,',',1)||'

order by '||split_part(icolumnas,',',1)||')a';

EXECUTE vquery

into vnum_columnas;

--****GENERAMOS CABECERAS CON NOMBRE CORRECTO****

if cast(ibancolumnas as boolean)=cast('t' as boolean) then

vquery:='drop table if exists aux_cabecera;

create temporary table aux_cabecera as

select '||icolumnas||',substring(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace('||split_part(icolumnas, ',', 1)||','' '',''''),''1'',''''),''2'',''''),''3'',''''),''4'',''''),''5'',''''),''6'',''''),''7'',''''),''8'',''''),''9'',''''),''0'',''''),''-'',''''),1,4)

||cast((row_number() over (ORDER BY 1)) as varchar) as cab_corr,''cols''||cast((row_number() over (ORDER BY 1)) as varchar) as cab_sequencia,row_number() over (ORDER BY 1) as orden

from (

select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||'

order by '||icolumnas||')a;';

EXECUTE vquery;

else

vquery:='drop table if exists aux_cabecera;

create temporary table aux_cabecera as

select '||icolumnas||',substring(replace('||split_part(icolumnas, ',', 1)||',''-'',''''),1,4)

||cast((row_number() over (ORDER BY 1)) as varchar) as cab_corr,''cols''||cast((row_number() over (ORDER BY 1)) as varchar) as cab_sequencia,row_number() over (ORDER BY 1) as orden

from (

select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||'

order by '||icolumnas||')a;';

EXECUTE vquery;

end if;

end if;

--*generamos las cantidad de columnas salida

    i := 1;

    vcolumnas_salida:='';

    WHILE (i <= vnum_columnas)

    loop

    vacum:=',cols'||cast(i as varchar)||' integer';

    vcolumnas_salida:=vcolumnas_salida||vacum;

    i:=i+1;

    raise notice 'columnas salida: %', vcolumnas_salida;

    end loop;

--**************************************

--**************************************

--*generamos las consulta fila de salida

--*sin la primera fila equivalente nacional

--**************************************

--**************************************

    i := 1;

    vconsulta_salida:='';

    vconsulta_cabecera:='';

    vconsulta_pie:='';

    WHILE (i <= vnum_filas)

    loop

    vacum:=split_part(ifilas, ',', i);

    if i = 1 then

    vconsulta_cabecera:=vconsulta_cabecera||'''''|''''||'||vacum;

    vconsulta_pie:=vconsulta_pie||vacum;

    else

    vconsulta_cabecera:=vconsulta_cabecera||'||''''|''''||'||vacum;

    vconsulta_pie:=vconsulta_pie||','||vacum;

    end if;

    if i = vnum_filas then

	vconsulta_salida:=vconsulta_salida||'SELECT crossreporte.*

	FROM 

	crosstab(''select '||vconsulta_cabecera||'::text as filas,'||replace(icolumnas,',','||')||'::text as columnas,sum('||ivalores||') as valor

		  from '||itabla_nomb||'

		  group by '||vconsulta_pie||','||icolumnas||'

		  order by '||vconsulta_pie||','||icolumnas||''',''select '||replace(icolumnas,',','||')||'::text as columnas from '||itabla_nomb||' group by '||icolumnas||' order by '||icolumnas||''')

	as crossreporte(filas varchar'||vcolumnas_salida||')';

     else

	vconsulta_salida:=vconsulta_salida||'SELECT crossreporte.*

	FROM 

	crosstab(''select '||vconsulta_cabecera||'::text as filas,'||replace(icolumnas,',','||')||'::text as columnas,sum('||ivalores||') as valor

		  from '||itabla_nomb||'

		  group by '||vconsulta_pie||','||icolumnas||'

		  order by '||vconsulta_pie||','||icolumnas||''',''select '||replace(icolumnas,',','||')||'::text as columnas from '||itabla_nomb||' group by '||icolumnas||' order by '||icolumnas||''')

	as crossreporte(filas varchar'||vcolumnas_salida||')

	union all 

	';

     end if;

    i:=i+1;

    end loop;

--**********************************************************************************

--*CORREGIMOS EL NOMBRE DE LAS CABECERAS CON LE CORRECTO SELECCIONADO POR EL USUARIO

--**********************************************************************************

vquery_cabecera:='select cab_corr,cab_sequencia from aux_cabecera order by orden desc;';

OPEN v_cursor_cab FOR EXECUTE vquery_cabecera;

vquery_cabecera :='';

loop

	FETCH v_cursor_cab INTO Icab_corr,Icab_sequencia;

	EXIT WHEN NOT FOUND;

	vcolumnas_salida:=replace(vcolumnas_salida,Icab_sequencia,Icab_corr);

end loop;

close v_cursor_cab;

raise notice 'consulta: %', vcolumnas_salida;

--**********************************************************

--*GENERACION DEL REPORTE CON LA CANITDAD DE FILAS COMPLETAS

--**********************************************************

vquery:='drop table if exists __data_warehouse.__auxiliar;

create table __data_warehouse.__auxiliar as

select (row_number() over (ORDER BY 1)) as nro,*

from (

select *

from (

SELECT crossreporte.*

	FROM 

	crosstab(''select ''''0''''||''''|''''::text as filas,'||replace(icolumnas,',','||')||'::text as columnas,sum('||ivalores||') as valor

		  from '||itabla_nomb||' group by '||icolumnas||''',''select '||replace(icolumnas,',','||')||'::text as columnas from '||itabla_nomb||' group by '||icolumnas||' order by '||icolumnas||''')

as crossreporte(filas text'||vcolumnas_salida||')

union all

'||vconsulta_salida||'

)a

order by filas)a;';

EXECUTE vquery;

--********************************************************************

--*incorporamos las sumatorias a las columnas de la segunda dimension

--********************************************************************

if  split_part(icolumnas, ',', 2)='' then

vquery:='select count(*) as num_reg

from (select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||') a';

EXECUTE vquery

into vnum_columnas;

ban:=1;

else

vquery:='select sum(num_col) as total_col

from (

select '||split_part(icolumnas,',',1)||',count(*) as num_col

from (

select '||icolumnas||'

from '||itabla_nomb||'

 group by '||icolumnas||'

 order by '||icolumnas||') a

group by '||split_part(icolumnas,',',1)||'

order by '||split_part(icolumnas,',',1)||')a';

EXECUTE vquery

into vnum_columnas;

ban:=2;

end if;

--*generamos la sumatoria total cantidad de columnas salida

    i := 1;

    vsum_total:='';

    vconsulta_cabecera_sum_total:='';

    WHILE (i <= vnum_columnas)

    loop

    if i = vnum_columnas then

    vacum:='COALESCE(cols'||cast(i as varchar)||',0)';

    vsum_total:=vsum_total||vacum;

    else

    vacum:='COALESCE(cols'||cast(i as varchar)||',0)+';

    vsum_total:=vsum_total||vacum;

    end if;

    vconsulta_cabecera_sum_total:=vconsulta_cabecera_sum_total||',cols'||cast(i as varchar);

 --   raise notice 'vconsulta_cabecera: %', vconsulta_cabecera_sum_total;

    i:=i+1;

    end loop;

    vsum_total:=',('||vsum_total||') as sumT';

if ban=2 then

--*generamos la sumatoria parcial con cantidad de columnas salida

vquery:='select count(*) as cant_num_reg

from (

select '||split_part(icolumnas,',',1)||',cast(count(*) as varchar) as num_reg

from (

select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||'

order by '||icolumnas||') a

group by '||split_part(icolumnas,',',1)||') a';

EXECUTE vquery

into vcant_num_reg;

vquery:='select string_agg(num_reg, '','')

from (

select '||split_part(icolumnas,',',1)||',cast(count(*) as varchar) as num_reg

from (

select '||icolumnas||'

from '||itabla_nomb||'

group by '||icolumnas||'

order by '||icolumnas||') a

group by '||split_part(icolumnas,',',1)||'

order by '||split_part(icolumnas,',',1)||') a';

EXECUTE vquery

into vacum_num_reg;

    i := 1;

    k := 1;

    l := 1;

    vconsulta_cabecera:='';

    vconsulta_cabecera_sum_total:='';

    WHILE (i <= vcant_num_reg)

    loop

    j:=1;

    vconsulta_cabecera:='';

    vconsulta_cabecera_sum:='';

	WHILE (j <= to_number(split_part(vacum_num_reg, ',', i),'999'))

	loop

        vconsulta_cabecera:=vconsulta_cabecera||',cols'||cast(k as varchar);

        vconsulta_cabecera_sum:=vconsulta_cabecera_sum||'COALESCE(cols'||cast(k as varchar)||',0)+';

	j:=j+1;

        k:=k+1;

	end loop;

        vconsulta_cabecera_sum:=',('||substring(vconsulta_cabecera_sum,1,char_length(vconsulta_cabecera_sum)-1)||') as sum'||cast(l as varchar);

        vconsulta_cabecera_sum_total:=vconsulta_cabecera_sum_total||vconsulta_cabecera||vconsulta_cabecera_sum;

	--raise notice 'Salida cols: %', vconsulta_cabecera;

        --raise notice 'Salida sum: %', vconsulta_cabecera_sum;

	--raise notice 'Salida tot: %', vconsulta_cabecera_sum_total;

    i:=i+1;

    l:=l+1;

    end loop;

end if;

vconsulta_cabecera_sum_total:=vconsulta_cabecera_sum_total||vsum_total;

raise notice 'Salida general: %', vconsulta_cabecera_sum_total;

--************************************

--*GENERAMOS LAS FILAS PARA EL REPORTE

--************************************

vnum_filas:=(select char_length(trim(ifilas)))-(select char_length(trim(replace(ifilas,',',''))))+1;

vaux_filas:='Total,'||ifilas;

    i := 1;

    vfilas_salida:='';

    WHILE (i <= vnum_filas)

    loop

    vacum:=',split_part(filas,''|'','||cast(i as varchar)||') as '||substring(vaux_filas, 0, position(',' in vaux_filas));

    vaux_filas:=replace(vaux_filas,substring(vaux_filas, 0, position(',' in vaux_filas))||',','');

    vfilas_salida:=vfilas_salida||vacum;

    i:=i+1;

    raise notice 'columnas vacum: %',vacum;

    raise notice 'columnas filas: %',vaux_filas;

    raise notice 'columnas salida: %', substring(vaux_filas, 0, position(',' in vaux_filas));

    end loop;

    vacum:=',split_part(filas,''|'','||cast(i as varchar)||') as '||vaux_filas;

    vfilas_salida:=vfilas_salida||vacum;

    raise notice 'columnas vacum: %',vacum;

    raise notice 'columnas filas: %',vaux_filas;

    raise notice 'columnas salida: %', substring(vaux_filas, 0, position(',' in vaux_filas));

    raise notice 'FILAS : %', vfilas_salida;

--**********************************************************************************

--*CORREGIMOS EL NOMBRE DE LAS CABECERAS CON LE CORRECTO SELECCIONADO POR EL USUARIO

--**********************************************************************************

vquery_cabecera:='select cab_corr,cab_sequencia from aux_cabecera order by orden desc;';

OPEN v_cursor_cab FOR EXECUTE vquery_cabecera;

vquery_cabecera :='';

loop

	FETCH v_cursor_cab INTO Icab_corr,Icab_sequencia;

	EXIT WHEN NOT FOUND;

	vconsulta_cabecera_sum_total:=replace(vconsulta_cabecera_sum_total,Icab_sequencia,Icab_corr);

end loop;

close v_cursor_cab;

raise notice 'consulta: %', vconsulta_cabecera_sum_total;

vquery:='drop table if exists '||itabla_salida||';

create table '||itabla_salida||' as

select nro'||vfilas_salida||vconsulta_cabecera_sum_total||'

from __data_warehouse.__auxiliar';

EXECUTE vquery;

RETURN;

END;$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION __data_warehouse.sp_genera_cubos_multidimensionales(character varying, character varying, character varying, character varying, character varying, character varying, character varying)

  OWNER TO eespejo;