2
In a function I want to put the parameters that would:
Number of Plots and Total Value
My problem would be how to generate maturity dates of installments
in Postgresql how to work with the dates to generate the plots
Ex: 03 plots
13.11.2019
13.12.2019
13.01.2020
How to generate dates based on the number of plots ?
Thanks for the help
How was your help
CREATE OR REPLACE FUNCTION public.gerar_parcelas (
p_data_vencimento date,
p_parcelas integer,
p_tipo_intervalo integer,
p_dias_intervalo integer,
p_dias_para_1_parcela integer,
p_documento integer
)
RETURNS TABLE (
data_vencimento date,
documento varchar
) AS
$body$
declare
contador integer;
dia integer;
mes integer;
ano integer;
script text;
resulta date;
r record;
BEGIN
-- Por Dia
if p_tipo_intervalo = 1 then
script = 'select '||''''||lpad(cast(p_documento as varchar),7,'0')||''''||'||'||''''||'.'||''''||'||lpad(cast(i+1 as VARCHAR),2,'||''''|| '0'||''''||')||'||''''||'/'||''''||'||'||''''||lpad(cast(p_parcelas as varchar),2,'0')||''''||' as documento,
'||''''||p_data_vencimento+p_dias_para_1_parcela||''''||'::date + ((i*'||p_dias_intervalo||')|| '||''''||' days'||''''||')::interval as data_venc
from generate_series(0,' || p_parcelas - 1 || ') as i;';
end if;
-- Por Mes
if p_tipo_intervalo = 2 then
script = 'select '||''''||p_data_vencimento+p_dias_para_1_parcela||''''||'::date + ((i)|| '||''''||' Months'||''''||')::interval as data_venc
from generate_series(0,' || p_parcelas - 1 || ') as i;';
end if;
for r in execute script loop
documento = r.documento;
data_vencimento = r.data_venc;
return next;
end loop;
END;
You want to use DATEADD (http://www.sqlines.com/postgresql/how-to/dateadd) But be sure to save the dates only after the operation occurs, if you spend a day your date calculation will be one day less, then I recommend to store the number of days in the bank until each maturity, and display the date in the view by calculating this in the code.
– Edi. Gomes
For each plot use:
sua_data_inicial + numero_da_parcela * (interval '1 month')
. Documentation.– anonimo
uses generate_series: http://sqlfiddle.com/#! 15/9eecb7db59d16c80417c72d1e1f4fbf1/29989
– Rovann Linhalis
I ran a test on him.. generate_series: only that it generated the dates with different days I did the example by putting for example 31.01.2020 with the parameter Interval 1 Month, what happens is that February it generates day 28 until there everything well more in March it also generates day 28 and it would have to be 31, would have to actually spawn on the same day.. and when that day doesn’t exist it would have to be a day later..
– Gustavo Castilho
The Idea of our friend Anomimo worked the way I wanted... I have to adapt to generate plot by plot.. the more did what I wanted to generate always on the same due day, the big problem is when is day 31 off that the generate_series would work perfectly
– Gustavo Castilho
http://sqlfiddle.com/#! 15/9eecb7db59d16c80417c72d1e1f4fbf1/29996 switched the generate series parameter, and generated 31 / 28 / 31 for 2019 and 31 / 29 / 31 for 2020
– Rovann Linhalis
Solved the Problem.... with generate_seriais.. I already made the function was very good... Thanks to all
– Gustavo Castilho