How to represent table generating functions?

[nightpool]

Hi all, i'm trying to convert the following snippet for generating a series of weeks into PRQL (postgres dialect)

with relevant_weeks as (
  select date(dt) week from generate_series('2022-06-03', date(date_trunc('week', current_date)) + 4, '1 week') as dt
),

With my first try, I ran into the following errors:

prql sql_dialect:postgres

func weeks_between start end -> s"generate_series({start}, {end}, '1 week') as date"
func current_week -> s"date(date_trunc('week', current_date))"

table relevant_weeks = (
  from (weeks_between @2022-06-03 (current_week + 4))
  select date
)

from relevant_weeks

 Error: 
   ╭─[:7:9]
   │
 7 │   from (weeks_between @2022-06-03 (current_week + 4))
   ·         ──────┬──────  
   ·               ╰──────── expected a function, but found `default_db.weeks_between`
───╯

How can I resolve this error? What is the best way to represent this sequence?

[aljazerzen]

from is meant for pulling data from tables it's actually not needed in your case.

But if you remove it, PRQL gives an error:

Error: 
   ╭─[:7:3]
   │
 7 │   weeks_between @2022-06-03 (current_week + 4)
   ·   ──────────────────────┬─────────────────────  
   ·                         ╰─────────────────────── table s-strings cannot contain interpolations
   · 
   · Help: are you missing `from` statement?
───╯

... which is a limitation we no longer need, so I just removed it.

Your query should now work, if you also prepend generate_series with SELECT (this is current implementation limitation):

prql sql_dialect:postgres

func weeks_between start end -> s"SELECT generate_series({start}, {end}, '1 week') as date"
func current_week -> s"date(date_trunc('week', current_date))"

table relevant_weeks = (
  weeks_between @2022-06-03 (current_week + 4)
  select date
)

from relevant_weeks