Re: [pgsql-es-ayuda] retornar una tabla dinámica

a ver, este es el procedimiento almacenado que tengo que cambiar…

create or replace function sp_rep_JornadaDeTrabajo(p_perspective_id bigint,
p_empleado bigint,
p_jt_id bigint,
p_buque bigint,
p_pt_clave bigint)
returns table(
devengo_basico_desde timestamp,
devengo_basico_hasta timestamp,
devengo_basico_tipo text,
devengo_complementario_desde timestamp,
devengo_complementario_hasta timestamp,
devengo_complementario_tipo text,
tarifas_varias_desde timestamp,
tarifas_varias_hasta timestamp,
tarifas_varias_tipo text,
lsi_desde timestamp,
lsi_hasta timestamp,
lsi_tipo text,
acumulaciones_variables_desde timestamp,
acumulaciones_variables_hasta timestamp,
acumulaciones_variables_tipo text,
primer_nombre text,
segundo_nombre text,
primer_apellido text,
segundo_apellido text,
empleado_clave bigint,
puesto_trabajo_clave bigint,
puesto_trabajo_descripcion text,
buque_clave bigint,
buque_descripcion text,
jt_id bigint,
jt_clave text,
jt_descripcion text) as
$$
declare
query_str text;
begin
query_str := 'with devengo_basico as (select
co0060ca01scaq.desde devengo_basico_desde,
co0060ca01scaq.hasta devengo_basico_hasta,
co0030ca02scag.descripcion devengo_basico_tipo,
co0060ca01scaq.fk165_co0050ca02scaa_clave,
co0060ca01scaq.fk474_co0010ca01scaa_clave,
co0060ca01scaq.fk639_co0040ca02scaa_clave,
co0060ca01scaq.fk333_co0060ca01scaa_id,
co0060ca01scaq.fk503_co0030ca02scaa_clave
from
co0060ca01scaq
inner join
co0060ca01scag on co0060ca01scag.fk503_co0030ca02scaa_clave = co0060ca01scaq.fk503_co0030ca02scaa_clave and
co0060ca01scag.fk474_co0010ca01scaa_clave = co0060ca01scaq.fk474_co0010ca01scaa_clave and
co0060ca01scag.fk639_co0040ca02scaa_clave = co0060ca01scaq.fk639_co0040ca02scaa_clave and
co0060ca01scag.fk333_co0060ca01scaa_id = co0060ca01scaq.fk333_co0060ca01scaa_id and
co0060ca01scag.removed = false
inner join
co0060ca01scae on co0060ca01scae.fk503_co0030ca02scaa_clave = co0060ca01scag.fk503_co0030ca02scaa_clave and
co0060ca01scae.fk474_co0010ca01scaa_clave = co0060ca01scag.fk474_co0010ca01scaa_clave and
co0060ca01scae.fk639_co0040ca02scaa_clave = co0060ca01scag.fk639_co0040ca02scaa_clave and
co0060ca01scae.removed = false
inner join
co0060ca01scab on co0060ca01scab.fk474_co0010ca01scaa_clave = co0060ca01scae.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave = co0060ca01scae.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scaa on co0030ca02scaa.clave = co0060ca01scae.fk503_co0030ca02scaa_clave and co0030ca02scaa.removed = false
inner join
co0030ca02scag on co0030ca02scag.clave = co0030ca02scaa.fk537_co0030ca02scag_clave and co0030ca02scag.removed = false
inner join
getperspectivecontent(' || p_perspective_id || ') on persp_id = co0060ca01scaq.perspective_id
where co0060ca01scaq.removed = false),
devengo_complementario as (select
co0060ca01scar.desde devengo_complementario_desde,
co0060ca01scar.hasta devengo_complementario_hasta,
co0030ca02scah.descripcion devengo_complementario_tipo,
co0060ca01scar.fk165_co0050ca02scaa_clave,
co0060ca01scar.fk474_co0010ca01scaa_clave,
co0060ca01scar.fk639_co0040ca02scaa_clave,
co0060ca01scar.fk333_co0060ca01scaa_id,
co0060ca01scar.fk543_co0030ca02scab_clave
from
co0060ca01scar
inner join
co0060ca01scah on co0060ca01scah.fk333_co0060ca01scaa_id = co0060ca01scar.fk333_co0060ca01scaa_id and
co0060ca01scah.fk474_co0010ca01scaa_clave = co0060ca01scar.fk474_co0010ca01scaa_clave and
co0060ca01scah.fk639_co0040ca02scaa_clave = co0060ca01scar.fk639_co0040ca02scaa_clave and
co0060ca01scah.fk543_co0030ca02scab_clave = co0060ca01scar.fk543_co0030ca02scab_clave and
co0060ca01scah.removed = false
inner join
co0060ca01scaf on co0060ca01scaf.fk474_co0010ca01scaa_clave = co0060ca01scah.fk474_co0010ca01scaa_clave and
co0060ca01scaf.fk639_co0040ca02scaa_clave = co0060ca01scah.fk639_co0040ca02scaa_clave and
co0060ca01scaf.fk543_co0030ca02scab_clave = co0060ca01scah.fk543_co0030ca02scab_clave and
co0060ca01scaf.removed = false
inner join
co0060ca01scab on co0060ca01scab.fk474_co0010ca01scaa_clave = co0060ca01scaf.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave = co0060ca01scaf.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scab on co0030ca02scab.clave = co0060ca01scaf.fk543_co0030ca02scab_clave and co0030ca02scab.removed = false
inner join
co0030ca02scah on co0030ca02scah.clave = co0030ca02scab.fk539_co0030ca02scah_clave and co0030ca02scah.removed = false
inner join
getperspectivecontent(' || p_perspective_id || ') on persp_id = co0060ca01scar.perspective_id
where co0060ca01scar.removed = false),
tarifas_varias as (select
co0060ca01scas.desde tarifas_varias_desde,
co0060ca01scas.hasta tarifas_varias_hasta,
co0030ca02scai.descripcion tarifas_varias_tipo,
co0060ca01scas.fk165_co0050ca02scaa_clave,
co0060ca01scas.fk474_co0010ca01scaa_clave,
co0060ca01scas.fk639_co0040ca02scaa_clave,
co0060ca01scas.fk333_co0060ca01scaa_id,
co0060ca01scas.fk514_co0030ca02scac_clave
from
co0060ca01scas
inner join
co0060ca01scai on co0060ca01scai.fk474_co0010ca01scaa_clave = co0060ca01scas.fk474_co0010ca01scaa_clave and
co0060ca01scai.fk639_co0040ca02scaa_clave = co0060ca01scas.fk639_co0040ca02scaa_clave and
co0060ca01scai.fk514_co0030ca02scac_clave = co0060ca01scas.fk514_co0030ca02scac_clave and
co0060ca01scai.fk333_co0060ca01scaa_id = co0060ca01scas.fk333_co0060ca01scaa_id and
co0060ca01scai.removed = false
inner join
co0060ca01scaj on co0060ca01scaj.fk474_co0010ca01scaa_clave = co0060ca01scai.fk474_co0010ca01scaa_clave and
co0060ca01scaj.fk639_co0040ca02scaa_clave = co0060ca01scai.fk639_co0040ca02scaa_clave and
co0060ca01scaj.fk514_co0030ca02scac_clave = co0060ca01scai.fk514_co0030ca02scac_clave and
co0060ca01scaj.removed = false
inner join
co0060ca01scab on co0060ca01scab.fk474_co0010ca01scaa_clave = co0060ca01scaj.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave = co0060ca01scaj.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scac on co0030ca02scac.clave = co0060ca01scaj.fk514_co0030ca02scac_clave and co0030ca02scac.removed = false
inner join
co0030ca02scai on co0030ca02scai.clave = co0030ca02scac.fk552_co0030ca02scai_clave and co0030ca02scai.removed = false
inner join
getperspectivecontent(' || p_perspective_id || ') on persp_id = co0060ca01scas.perspective_id
where co0060ca01scas.removed = false),
lsi as (select
co0060ca01scau.desde lsi_desde,
co0060ca01scau.hasta lsi_hasta,
co0030ca02scak.descripcion lsi_tipo,
co0060ca01scau.fk165_co0050ca02scaa_clave,
co0060ca01scau.fk474_co0010ca01scaa_clave,
co0060ca01scau.fk639_co0040ca02scaa_clave,
co0060ca01scau.fk333_co0060ca01scaa_id,
co0060ca01scau.fk525_co0030ca02scae_clave
from
co0060ca01scau
inner join
co0060ca01scak on co0060ca01scak.fk525_co0030ca02scae_clave = co0060ca01scau.fk525_co0030ca02scae_clave and
co0060ca01scak.fk474_co0010ca01scaa_clave = co0060ca01scau.fk474_co0010ca01scaa_clave and
co0060ca01scak.fk639_co0040ca02scaa_clave = co0060ca01scau.fk639_co0040ca02scaa_clave and
co0060ca01scak.fk333_co0060ca01scaa_id = co0060ca01scau.fk333_co0060ca01scaa_id and
co0060ca01scak.removed = false
inner join
co0060ca01scal on co0060ca01scal.fk525_co0030ca02scae_clave = co0060ca01scak.fk525_co0030ca02scae_clave and
co0060ca01scal.fk474_co0010ca01scaa_clave = co0060ca01scak.fk474_co0010ca01scaa_clave and
co0060ca01scal.fk639_co0040ca02scaa_clave = co0060ca01scak.fk639_co0040ca02scaa_clave and
co0060ca01scal.removed = false
inner join
co0060ca01scab on co0060ca01scab.fk474_co0010ca01scaa_clave = co0060ca01scal.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave = co0060ca01scal.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scae on co0030ca02scae.clave = co0060ca01scal.fk525_co0030ca02scae_clave and co0030ca02scae.removed = false
inner join
co0030ca02scak on co0030ca02scak.clave = co0030ca02scae.fk550_co0030ca02scak_clave and co0030ca02scak.removed = false
inner join
getperspectivecontent(' || p_perspective_id || ') on persp_id = co0060ca01scau.perspective_id
where co0060ca01scau.removed = false),

acumulaciones_variables as (select
co0060ca01scad.desde acumulaciones_variables_desde,
co0060ca01scad.hasta acumulaciones_variables_hasta,
co0030ca04scae.descripcion acumulaciones_variables_tipo,
co0060ca01scad.fk165_co0050ca02scaa_clave,
co0060ca01scad.fk474_co0010ca01scaa_clave,
co0060ca01scad.fk639_co0040ca02scaa_clave,
co0060ca01scad.fk333_co0060ca01scaa_id,
co0060ca01scad.fk1338_co0030ca04scad_clave
from
co0060ca01scad
inner join
co0060ca01scac on co0060ca01scac.fk333_co0060ca01scaa_id = co0060ca01scad.fk333_co0060ca01scaa_id and
co0060ca01scac.fk1338_co0030ca04scad_clave = co0060ca01scad.fk1338_co0030ca04scad_clave and
co0060ca01scac.fk474_co0010ca01scaa_clave = co0060ca01scad.fk474_co0010ca01scaa_clave and
co0060ca01scac.fk639_co0040ca02scaa_clave = co0060ca01scad.fk639_co0040ca02scaa_clave and
co0060ca01scac.removed = false
inner join
co0060ca01scam on co0060ca01scam.fk1338_co0030ca04scad_clave = co0060ca01scac.fk1338_co0030ca04scad_clave and
co0060ca01scam.fk474_co0010ca01scaa_clave = co0060ca01scac.fk474_co0010ca01scaa_clave and
co0060ca01scam.fk639_co0040ca02scaa_clave = co0060ca01scac.fk639_co0040ca02scaa_clave and
co0060ca01scam.removed = false
inner join
co0060ca01scab on co0060ca01scab.fk474_co0010ca01scaa_clave = co0060ca01scam.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave = co0060ca01scam.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca04scad on co0030ca04scad.clave = co0060ca01scam.fk1338_co0030ca04scad_clave and co0030ca04scad.removed = false
inner join
co0030ca04scae on co0030ca04scae.clave = co0030ca04scad.fk1333_co0030ca04scae_clave and co0030ca04scae.removed = false
inner join
getperspectivecontent(' || p_perspective_id || ') on persp_id = co0060ca01scad.perspective_id
where co0060ca01scad.removed = false)
select
devengo_basico.devengo_basico_desde,
devengo_basico.devengo_basico_hasta,
devengo_basico.devengo_basico_tipo,
devengo_complementario.devengo_complementario_desde,
devengo_complementario.devengo_complementario_hasta,
devengo_complementario.devengo_complementario_tipo,
tarifas_varias.tarifas_varias_desde,
tarifas_varias.tarifas_varias_hasta,
tarifas_varias.tarifas_varias_tipo,
lsi.lsi_desde,
lsi.lsi_hasta,
lsi.lsi_tipo,
acumulaciones_variables.acumulaciones_variables_desde,
acumulaciones_variables.acumulaciones_variables_hasta,
acumulaciones_variables.acumulaciones_variables_tipo,
co0050ca02scaa.primer_nombre,
co0050ca02scaa.segundo_nombre,
co0050ca02scaa.primer_apellido,
co0050ca02scaa.segundo_apellido,
co0050ca02scaa.clave empelado_clave,
co0040ca02scaa.clave puesto_trabajo_clave,
co0040ca02scaa.descripcion puesto_trabajo_descripcion,
co0010ca01scaa.clave buque_clave,
co0010ca01scaa.descripcion buque_descripcion,
co0060ca01scaa.id jt_id,
co0060ca01scaa.clave jt_clave,
co0060ca01scaa.descripcion jt_descripcion
from co0060ca01scao
inner join
co0060ca01scaa on co0060ca01scaa.id = co0060ca01scao.fk333_co0060ca01scaa_id and co0060ca01scaa.removed = false
inner join
co0050ca02scaa on co0050ca02scaa.clave = co0060ca01scao.fk165_co0050ca02scaa_clave and co0050ca02scaa.removed = false
inner join
co0040ca02scaa on co0040ca02scaa.clave = co0060ca01scao.fk639_co0040ca02scaa_clave and co0040ca02scaa.removed = false
inner join
co0010ca01scaa on co0010ca01scaa.clave = co0060ca01scaa.fk474_co0010ca01scaa_clave and co0010ca01scaa.removed = false
left join
devengo_basico on co0060ca01scao.fk165_co0050ca02scaa_clave = devengo_basico.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id = devengo_basico.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave = devengo_basico.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave = devengo_basico.fk639_co0040ca02scaa_clave
left join
devengo_complementario on co0060ca01scao.fk165_co0050ca02scaa_clave = devengo_complementario.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id = devengo_complementario.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave = devengo_complementario.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave = devengo_complementario.fk639_co0040ca02scaa_clave
left join
tarifas_varias on co0060ca01scao.fk165_co0050ca02scaa_clave = tarifas_varias.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id = tarifas_varias.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave = tarifas_varias.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave = tarifas_varias.fk639_co0040ca02scaa_clave
left join
lsi on co0060ca01scao.fk165_co0050ca02scaa_clave = lsi.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id = lsi.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave = lsi.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave = lsi.fk639_co0040ca02scaa_clave
left join
acumulaciones_variables on co0060ca01scao.fk165_co0050ca02scaa_clave = acumulaciones_variables.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id = acumulaciones_variables.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave = acumulaciones_variables.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave = acumulaciones_variables.fk639_co0040ca02scaa_clave
inner join
getperspectivecontent(' || p_perspective_id || ') on persp_id = co0060ca01scao.perspective_id

where co0060ca01scao.removed = false ';

if p_buque is not null
then
query_str := query_str || ' and co0010ca01scaa.clave = ' || p_buque;
end if;

if p_empleado is not null
then
query_str := query_str || ' and co0050ca02scaa.clave = ' || p_empleado;
end if;

if p_pt_clave is not null
then
query_str := query_str || ' and co0040ca02scaa.clave = ' || p_pt_clave;
end if;

if p_jt_id is not null
then
query_str := query_str || ' and co0060ca01scaa.clave = ' || p_jt_id;
end if;

return query execute query_str;
end;
$$
language plpgsql;
si te das cuenta, tengo una instrucción with en el query que estoy ejecutando… no había puesto el procedimiento porque la bd con la que trabaja el sistema tiene estos nombre extraños…

lo que necesito es agarrar os queries que están en el with i no hacer el left join que se está ejecutando, pues se multiplican los resultados, necesito agarrar y armar una tabla en la que yo inserte solamente el resultado de los registros del 1er with, luego del 2do, luego del 3ro… y asi sucesivamente, obviamente siempre que cumplan con la condición del with

eso es lo que necesito hacer

saludos

=============================================
"El tamaño de tus logros depende del tamaño de tus metas."
C++ and Qt Senior Developer
Lic. Computer Science
Buenos Aires, Argentina

> On May 28, 2015, at 5:17 PM, Gilberto Castillo <gilberto(dot)castillo(at)etecsa(dot)cu> wrote:
>
>
>
>> si, eso es lo que quiero hacer…. el tema es que aún así, por una demanda
>> de mi app, necesito organizarla bajo criterios que con un order by no
>> resuelvo… lo ideal para mi sería hacer los queries correspondiente a los 5
>> procesos diferentes en cuestion (porque son 5 queries básicamente los que
>> tengo que ejecutar), filtrar la info e ir armando el resulset que quiero
>> devolver… algo así es lo que quiero hacer…
>>
>> puedo hacerlo ??
>
> Todo, es posible, pero das muy poca info para inferir otras cosas.
>
> Saludos,
> Gilberto Castillo
> ETECSA, La Habana, Cuba
> ---
> This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
> Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>