Re: [pgsql-es-ayuda] Intentando referencias cruzadas.

[Gabriel Hermes Colina Zambra]

--- El mié 19-nov-08, Pablo Braulio <[EMAIL PROTECTED]> escribió:

> De: Pablo Braulio <[EMAIL PROTECTED]>
> Asunto: Re: [pgsql-es-ayuda] Intentando referencias cruzadas.
> A: "Foro PostgreSQL" <pgsql-es-ayuda@postgresql.org>
> Fecha: miércoles, 19 noviembre, 2008, 5:59 am
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Bueno, lo tengo medio solucionado.
> 
> He visto en la documentación las funciones de tablefunc
> y el uso de
> crosstab.
> 
> He construido el siguiente query.
> 
> create view vista_prueba as
> select e.nombre, e.apellidos, f.nombre as fichero, case
> when
> fe.empleado=e.id and
> fe.fichero=f.id then 'Si' else 'No' end as
> Acceso
> from empleados e, ficheros f, ficheros_empleados fe;
> 
> Esto me devuelve la siguiente tabla:
> 
> SELECT * from vista_prueba ;
>   nombre   |  apellidos   |     fichero      | acceso
> - -----------+--------------+------------------+--------
>  MARIA     | TIGRE        | fichero de ...   | No
>  MARIA     | TIGRE        | clientes         | No
>  MANOLILLO | GAFOTAS      | fichero de ...   | No
>  MANOLILLO | GAFOTAS      | clientes         | Si
>  Manuel    | Garcia Lopez | fichero de ...   | No
>  Manuel    | Garcia Lopez | clientes         | No
> (6 rows)
> 
> Luego con esto puedo ver los datos como quiero, mas o
> menos:
> 
> select *
> from crosstab('select nombre::text, fichero::text,
> acceso::text from
> vista_prueba where fichero is not null order by
> nombre')
> as vista_prueba(nombre text, fichero de... text, clientes
> text);
> 
>   nombre   | fichero de ... | clientes
> - -----------+----------------+----------
>  MANOLILLO | No             | Si
>  Manuel    | No             | No
>  MARIA     | No             | No
> (3 rows)
> 
> Podría agrupar los campos nombre y apellidos en la vista
> para que se
> vean luego al usar crosstab.
> 
> El caso es que de este modo tengo que introducir
> manualmente el nombre
> de los campos fichero de ... y clientes. Lo que quisiera es
> poder
> sacarlos ya que son valores de la tabla ficheros.
> 
> ¿Alguna idea?.
> - --
> Saludos cordiales.
Esto fuen un post mio y hasta donde pude llegar solito, en aquel tiempo me 
falto algo de conocimiento de execute, pero te mando lo que postee ultimo sobre 
el tema]

Resulta que hace tiempo queria mejorar nuestra
contribucion crosstab, por que pienso que esa es una
debilidad no resuelta con respcto al pivot table de
access.

Luego de trabajar mucho con ella, lo deje en un cajon
hasta que Alvaro me pregunto en que quedo el proyecto,
lo desempolve y ni bien empeze tome otro camino. 

Ahora les cuento la idea.

select
public.pivot_table2('central.cabezales','id_empresa','id_documentos','importe_con_impuestos','ref');

Parametros 
1 cualquier nombre de tabla o vista
2 campo de la columna 1 de la tabla curzada
3 campo 2 el que va a formar tantas columnas como
valores variantes hallan en ese campo
4 campo a sumar,contar,promediar etc.
(en esta funcion que paso solo suma)
5 parametro para nombre de cursor.
El cual no logro tomar.

Entonces con todo esto procesado en

FOR mvista IN execute 'SELECT '|| tcampo2 ||' as
campo2 FROM '|| tvista_tabla
|| ' GROUP BY '|| tcampo2  ||  ' ORDER BY ' || tcampo2
||';'
LOOP
nI:=nI + 1;
tcuerpo:=tcuerpo || 'sum(case when '|| tcampo2 || '='
|| coalesce(mvista.campo2,0) || ' then ' || tcampo3 ||
' else 0 end) as ' || tcampo2 ||
coalesce(mvista.campo2,0) || ',';
END LOOP;

Logro para este caso formar esta consulta, sin saber
cuantos valores tengo en id_documentos en este caso en
campo2 para cualquier caso logico de ser procesado en
una crosstab, pero a diferencia de la que tenemos como
dije aca no necesito saber a priori esto.

El resultado para el execute final para devolver el
cursor es el siguiente en este caso, lo grabe en un
campo text con un insert para poder mostrarlo.
perdon por lo extenso pero vale la pena

La funcion es esta

CREATE OR REPLACE FUNCTION "public"."pivot_table2"
(tvista_tabla text, tcampo1 text, tcampo2 text,
tcampo3 text, ref "pg_catalog"."refcursor") RETURNS
"pg_catalog"."refcursor" AS
$body$
DECLARE
tvista_tabla alias for $1;
tcampo1 alias for $2;
tcampo2 alias for $3;
tcampo3 alias for $4;
tcuerpo text;
mvista RECORD;
ref alias for $5;
nI integer;
BEGIN
nI:=0;
tcuerpo:='';
FOR mvista IN execute 'SELECT '|| tcampo2 ||' as
campo2 FROM '|| tvista_tabla
|| ' GROUP BY '|| tcampo2  ||  ' ORDER BY ' || tcampo2
||';'
LOOP
nI:=nI + 1;
tcuerpo:=tcuerpo || 'sum(case when '|| tcampo2 || '='
|| coalesce(mvista.campo2,0) || ' then ' || tcampo2 ||
' else 0 end) as ' || tcampo2 ||
coalesce(mvista.campo2,0) || ',';
END LOOP;
open ref for execute 'select ' || tcampo1 || ',' ||
tcuerpo || '0 as total from ' || tvista_tabla || '
GROUP BY '|| tcampo1  ||  ' ORDER BY ' || tcampo1;
RETURN ref;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER;

y este es el resultado que analiza el ultimo execute
el cursor llamado ref

select id_empresa,sum(case when id_documentos=1 then
id_documentos else 0 end) as id_documentos1,sum(case
when id_documentos=2 then id_documentos else 0 end) as
id_documentos2,sum(case when id_documentos=3 then
id_documentos else 0 end) as id_documentos3,sum(case
when id_documentos=4 then id_documentos else 0 end) as
id_documentos4,sum(case when id_documentos=5 then
id_documentos else 0 end) as id_documentos5,sum(case
when id_documentos=6 then id_documentos else 0 end) as
id_documentos6,sum(case when id_documentos=7 then
id_documentos else 0 end) as id_documentos7,sum(case
when id_documentos=9 then id_documentos else 0 end) as
id_documentos9,sum(case when id_documentos=10 then
id_documentos else 0 end) as id_documentos10,sum(case
when id_documentos=11 then id_documentos else 0 end)
as id_documentos11,sum(case when id_documentos=12 then
id_documentos else 0 end) as id_documentos12,sum(case
when id_documentos=13 then id_documentos else 0 end)
as id_documentos13,sum(case when id_documentos=15 then
id_documentos else 0 end) as id_documentos15,sum(case
when id_documentos=16 then id_documentos else 0 end)
as id_documentos16,sum(case when id_documentos=22 then
id_documentos else 0 end) as id_documentos22,sum(case
when id_documentos=25 then id_documentos else 0 end)
as id_documentos25,sum(case when id_documentos=30 then
id_documentos else 0 end) as id_documentos30,sum(case
when id_documentos=31 then id_documentos else 0 end)
as id_documentos31,sum(case when id_documentos=101
then id_documentos else 0 end) as
id_documentos101,sum(case when id_documentos=102 then
id_documentos else 0 end) as id_documentos102,sum(case
when id_documentos=103 then id_documentos else 0 end)
as id_documentos103,sum(case when id_documentos=105
then id_documentos else 0 end) as
id_documentos105,sum(case when id_documentos=106 then
id_documentos else 0 end) as id_documentos106,sum(case
when id_documentos=107 then id_documentos else 0 end)
as id_documentos107,sum(case when id_documentos=108
then id_documentos else 0 end) as
id_documentos108,sum(case when id_documentos=109 then
id_documentos else 0 end) as id_documentos109,sum(case
when id_documentos=110 then id_documentos else 0 end)
as id_documentos110,sum(case when id_documentos=111
then id_documentos else 0 end) as
id_documentos111,sum(case when id_documentos=112 then
id_documentos else 0 end) as id_documentos112,sum(case
when id_documentos=113 then id_documentos else 0 end)
as id_documentos113,sum(case when id_documentos=114
then id_documentos else 0 end) as
id_documentos114,sum(case when id_documentos=115 then
id_documentos else 0 end) as id_documentos115,sum(case
when id_documentos=302 then id_documentos else 0 end)
as id_documentos302,sum(case when id_documentos=303
then id_documentos else 0 end) as
id_documentos303,sum(case when id_documentos=330 then
id_documentos else 0 end) as id_documentos330,sum(case
when id_documentos=331 then id_documentos else 0 end)
as id_documentos331,sum(case when id_documentos=0 then
id_documentos else 0 end) as id_documentos0,0 as total
from central.cabezales GROUP BY id_empresa ORDER BY
id_empresa

select
public.pivot_table2('central.cabezales','id_empresa','id_documentos','importe_con_impuestos','ref');

Veo pivot_table2
    ref
Despues hago asi

FETCH ALL IN ref;
Cursor ref no existe es lo que me devuelve

Creo que es una lastima no seguir profundizando por
estar trancado aqui, por que aunque muy rudimentario
por ahora, esta seria una buena solucion


Atte.
Gabriel Colina





      
____________________________________________________________________________________
Premios MTV 2008¡En exclusiva! Fotos, nominados, videos, y mucho más! Mira aquí 
http://mtvla.yahoo.com/
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net