last / first / nth not working with PostgreSQL

[florianWickler]

Please have a look at
http://stackoverflow.com/questions/25184965/r-dplyr-how-to-use-last-first-nth-correct-in-a-sql-database

 library("dplyr")
 mytab <- tbl(src_postgres(dbname, host, port, user, password), "catalog")
 mytab <- group_by(mytab, catalog_id)
 mytab<- mutate(mytab, curr2 = last(currency))
 mytab

RS-DBI driver: (could not Retrieve the result : ERROR: function first_value() does not exist

mytab$query

SELECT "catalog_id", "currency", "skonto", FIRST_VALUE() OVER (PARTITION BY "catalog_id") AS "curr2" FROM "catalog"

In my opinion the query should be:

SELECT "catalog_id", "currency", "skonto", FIRST_VALUE(currency) OVER (PARTITION BY "catalog_id") AS "curr2" FROM "catalog"

[hadley]

What version of postgresql do you have? Looks like it's available from at least 9.0 on. http://www.postgresql.org/docs/9.0/static/functions-window.html.

Could you please provide a reproducible example?

[florianWickler]

I'm using PostgreSQL 9.3.2.
In my opinion the dplyrs' SQL translator is wrong because the PostgreSQL-DB expects a value argument for the statement first_value etc. But you don't assign it:
FIRST_VALUE(currency) instead of FIRST_VALUE().

I don' t know how to provide a reproducible example at the moment.

[hadley]

Simple reproducible example:

mtcars2 <- copy_to(src_postgres(), mtcars)
mtcars2 %>%
  group_by(cyl) %>% 
  mutate(mpg2 = last(mpg))