CODEWITHSUNDEEP
postgresqlplpgsql
meyquel
meyquel

Reputation: 2214

How to iterate to obtain values in each iteration and to return them in a list

I have a list of people and I want to get the number of people grouped by years that the property date is in that year. The problem is that the amount of years I want to look for is from the current backwards I wish the user to select it. Currently I get the information for 10 years ago but I wish the user can enter any number of years.

CREATE OR REPLACE FUNCTION convertidos_neto_10_year_ago(IN aidiglesia integer)

  RETURNS TABLE(cant0 bigint, cant1 bigint, cant2 bigint, cant3 bigint, cant4 bigint, cant5 bigint, cant6 bigint, cant7 bigint, cant8 bigint, cant9 bigint, cant10 bigint) AS

$BODY$BEGIN

RETURN Query SELECT

 ( SELECT  count(jb_persona.id)
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = date_part('year'::text, ('now'::text)::date) - (10)::double precision))) ,

 ( SELECT count(jb_persona.id)
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (9)::double precision)))) ,

( SELECT count(jb_persona.id) AS count3
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (8)::double precision)))) ,

( SELECT count(jb_persona.id) AS count4
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (7)::double precision))))  ,

 ( SELECT count(jb_persona.id) AS count5
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (6)::double precision)))) ,

 ( SELECT count(jb_persona.id) AS count5
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (5)::double precision)))) ,

( SELECT count(jb_persona.id) AS count5
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (4)::double precision)))) ,

  ( SELECT count(jb_persona.id) AS count5
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (3)::double precision)))) ,

 ( SELECT count(jb_persona.id) AS count5
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (2)::double precision)))) ,

( SELECT count(jb_persona.id) AS count5
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (1)::double precision)))) ,

 ( SELECT count(jb_persona.id) AS count5
           FROM jb_persona
          WHERE ((jb_persona.id_iglesiafk = aidiglesia) AND (date_part('year'::text, jb_persona.fecha_conversion) = (date_part('year'::text, ('now'::text)::date) - (0)::double precision))));  

END;

$BODY$

  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

ALTER FUNCTION convertidos_neto_10_year_ago(integer)

 OWNER TO postgres;

Upvotes: 0

Views: 63

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125274

To also have zero count years at the output outer join a generated year series:

select to_char(d, 'YYYY') as year, count(id) as total
from
    jb_persona
    right join
    generate_series(
        date_trunc('year', now() - interval '10 years'),
        now(), interval '1 year'
    ) gs(d) on gs.d = date_trunc('year', fecha_conversion)
where id_iglesiafk = aidiglesia
group by 1

If you want that as columns the easiest solution it to have it as json:

select jsonb_object_agg(year, total)
from (
    select to_char(d, 'YYYY') as year, count(id) as total
    from
        jb_persona
        right join
        generate_series(
            date_trunc('year', now() - interval '10 years'),
            now(), interval '1 year'
        ) gs(d) on gs.d = date_trunc('year', fecha_conversion)
    where id_iglesiafk = aidiglesia
    group by 1
) s

For 9.4 use:

select jsonb_object(array_agg(year), array_agg(total::text))
from ...

Upvotes: 1

Related Questions