2014-05-21 16:27 GMT-03:00 Pedro B. Alves <[email protected]>: > >> CREATE OR REPLACE FUNCTION pedidos_mes(dt_ini date, dt_fim date) >> RETURNS TABLE(month integer, year integer, count integer) >> LANGUAGE SQL >> AS $$ >> SELECT mes, ano, sum(count) >> FROM ( >> SELECT EXTRACT(MONTH FROM datapedido) AS mes, EXTRACT(YEAR FROM >> datapedido) AS ano, COUNT(id) AS count >> FROM pedidos >> WHERE datapedido BETWEEN dt_ini AND dt_fim >> GROUP BY ano, mes >> UNION ALL >> SELECT EXTRACT(MONTH FROM dt), EXTRACT(YEAR FROM dt), 0 >> FROM generate_series(date_trunc('month', dt_ini), >> date_trunc('month', dt_fim), interval '1 month'); >> ) t >> ORDER BY ano, mes >> GROUP BY ano, mes >> $$; >> >> Dessa forma você pega tudo disponível na tabela e usa o generate_series >> para gerar as datas (nesse caso primeiro dia de cada mês) que não estão lá. >> >> > Acredito ser isso. Vou testar aqui. >
a função está me retornando um RECORD "(1,2014,0)" "(2,2014,0)" "(3,2014,0)" "(4,2014,3)" "(5,2014,0)" Sabes como resolver isso?
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
