3

I have a postgres query, which works fine when I run it as a query. However, I would like to convert it to pl/r and be able to dynamically input the start and end date.

The SQL that works is:

with date as (
select d as first_day,
d + interval '1 month' - interval '1 day' as last_day
from generate_series('2010-01-01'::date,
                     '2018-12-01'::date,
                     '1 month') as d
) select last_day::date as snapshot_date from date;

Would like to make a pl/r like:

DROP FUNCTION IF EXISTS standard.seq_monthly(min_date_str char, max_date_str char);
CREATE FUNCTION standard.seq_monthly(min_date_str char, max_date_str char)
RETURNS setof dates AS
$$ 
with date as (
select d as first_day,
d + interval '1 month' - interval '1 day' as last_day
from generate_series(min_date_str::date,
                     max_date_str::date,
                     '1 month') as d
) select last_day::date as snapshot_date from date;

$$
LANGUAGE 'plr';
select * from standard.seq_monthly('2010-01-01' , '2018-12-01')

However, I am getting error while running the function. The error is

R parse error caught in "PLR711818 <- function(min_date_str,max_date_str)

Have tried declaring the max min date to be date as well.

Any help is highly appreciated.

camille
  • 16,432
  • 18
  • 38
  • 60
Bruce Wayne
  • 471
  • 5
  • 18
  • 1
    `PL/R` expects R code not SQL. By the way, it is more efficient to use SQL than any procedural extensions including Postgres' built-in plpgsql. – Parfait Dec 10 '18 at 19:56
  • BTW:`date` is *almost* a reserved word. You may lose, now or in the future. – wildplasser Dec 11 '18 at 00:42

2 Answers2

2

PL/R is a procedural language extension in PostgreSQL (similar to plpython, plperl, plphp) where valid, compliant R language code can be run. You are attempting SQL which cannot by itself run inside an R session, so your code will fail within a PG plr stored function.

However, there is no need for such an extension as your needs can be handled with the very basic SQL language (often more times efficient) to return needed date range table by specified input range:

CREATE OR REPLACE FUNCTION seq_monthly(min_date_str char, max_date_str char)
RETURNS TABLE(snapshot_date date) AS
$$ 
     with mydate as (
          select d as first_day,
                 DATE_TRUNC('month', d) 
                     + '1 MONTH'::INTERVAL 
                     - '1 DAY'::INTERVAL as last_day
          from generate_series(min_date_str::date,
                               max_date_str::date,
                               '1 month') as d
     ) 

     select last_day::date as snapshot_date from mydate;

$$
LANGUAGE SQL STABLE;

select * from seq_monthly('2010-01-31' , '2018-12-31');

Rextester demo

Now, if your really want a plr stored function, use R's seq() on given date range:

CREATE FUNCTION standard.seq_monthly(min_date_str char, max_date_str char)
RETURNS setof dates AS
$$ 
    seq(as.Date(min_date_str), as.Date(max_date_str), by='month')
$$
LANGUAGE 'plr';

select * from standard.seq_monthly('2010-01-01' , '2018-12-01')
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • somehow, either of these are not working on postgres for me. Also, the error does not have any explanation. Just says Error! Might be me or my version of postgres. Would check again shortly – Bruce Wayne Dec 10 '18 at 22:46
  • on rextester demo link, try passing the last dates of the month – Bruce Wayne Dec 11 '18 at 01:00
  • That's because of lovely February with its shortened days and the pattern follows suit thereafter. However, there is a better way with `DATE_TRUNC()`. See updated answer and demo link. – Parfait Dec 12 '18 at 15:07
  • Perfect! Thank you!! – Bruce Wayne Dec 12 '18 at 17:10
  • do you have any resources I could use for pl/r? – Bruce Wayne Dec 20 '18 at 18:55
  • 1
    Use the author's [site](http://www.joeconway.com/plr.html) which includes html/pdf docs including installation steps. By the way the author is the same one who helped with *RPostgreSQL*, R package. – Parfait Dec 20 '18 at 18:58
  • Thank you, I will take a look. Have been following him. Looking specifically of how to call one pl/r function into another. – Bruce Wayne Dec 20 '18 at 19:25
  • Ouch! Why the question loss? Solution stopped working for you? – Parfait Mar 28 '19 at 00:53
1

One of the solutions is to not go via plr, but write a sql query:

with max_min_date as(
select max(snapshot_date) as max_date_str, min(snapshot_date) as min_date_str from data
) , 
ts as (
select d as first_day,
d + interval '1 month' - interval '1 day' as last_day
from generate_series((select min_date_str from max_min_date)::date,
                     (select max_date_str from max_min_date)::date,
                     '1 month') as d
) select last_day::date as snapshot_date from ts;

In case, you'd need end of month date instead of start of month:

with max_min_date as(
  with max_min_wrk as (
    select max(snapshot_date) as max_date_str, min(snapshot_date) as min_date_str from data
    ) select cast(date_trunc('month', max_date_str) as date) as max_date, cast(date_trunc('month', min_date_str) as date) as min_date from max_min_wrk
), 
ts as (
select d as first_day,
d + interval '1 month' - interval '1 day' as last_day
from generate_series((select min_date from max_min_date)::date,
                     (select max_date from max_min_date)::date,
                     '1 month') as d
) select last_day::date as snapshot_date from ts 
order by snapshot_date asc;
Bruce Wayne
  • 471
  • 5
  • 18