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

Hola Freddy, no te sirve crear tablas temporales?
saludos

On 28/05/15 16:24, Freddy Martinez Garcia wrote:
> a ver, este es el procedimiento almacenado que tengo que cambiar…
>
> create or replace functionsp_rep_JornadaDeTrabajo(p_perspective_idbigint,
> p_empleadobigint,
> p_jt_idbigint,
> p_buquebigint,
> p_pt_clavebigint)
> returns table(
> devengo_basico_desdetimestamp,
> devengo_basico_hastatimestamp,
> devengo_basico_tipotext,
> devengo_complementario_desdetimestamp,
> devengo_complementario_hastatimestamp,
> devengo_complementario_tipotext,
> tarifas_varias_desdetimestamp,
> tarifas_varias_hastatimestamp,
> tarifas_varias_tipotext,
> lsi_desdetimestamp,
> lsi_hastatimestamp,
> lsi_tipotext,
> acumulaciones_variables_desdetimestamp,
> acumulaciones_variables_hastatimestamp,
> acumulaciones_variables_tipotext,
> primer_nombretext,
> segundo_nombretext,
> primer_apellidotext,
> segundo_apellidotext,
> empleado_clavebigint,
> puesto_trabajo_clavebigint,
> puesto_trabajo_descripciontext,
> buque_clavebigint,
> buque_descripciontext,
> jt_idbigint,
> jt_clavetext,
> jt_descripciontext)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;
> $$
> languageplpgsql;
> 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 <mailto: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>
>