How to define variables in a PL/R (plr) function as parameters for a query inside

Question

I've written a function that queries x,y,z values for creating sections with R. Now, I want to do that with PL/R, instead of using RStudio.

CREATE OR REPLACE FUNCTION section_graph() RETURNS text AS
'
require(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host="localhost", user="postgres", password="...", dbname="...", port="5432")
rs <- dbSendQuery(con, "SELECT x,y,z FROM (SELECT (section(1,3)).*) AS foo")
section1 <- fetch(rs, 2000)
dbClearResult(rs)
dbDisconnect(con)
pdf("/tmp/myplot.pdf", width=18, height=9)
plot(section1$y, section1$z, type="l", lwd=1.5, lty=3)
dev.off()
print("done")
'
LANGUAGE 'plr';

Within the dbSendQuery command there is the query SELECT x,y,z FROM (SELECT (section(1,3)).*) AS foo. The paramter 1 represents an ID, the second one is the accuracy of the section (3m).

Now, I want to use my function above like a common PostgreSQL function (e.g. with language 'sql'). That means, a want to define parameters within the function like this:

SELECT x,y,z FROM (SELECT (section($1,$2)).*) AS foo

$1 $2 are the parameters for my function section_graph.

Is this possible with the language 'plr'? I didn't found something helpful.

Obviously there are problems with the double quotes of the query within the function.

Answer 1

Did you try looking at the pl/r documentation? Took me about ten seconds. The parameters are either called arg1 to argN:

CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
    if (arg1 > arg2)
       return(arg1)
    else
       return(arg2)
' LANGUAGE 'plr' STRICT;

or, PG 8 and above, you can name them:

CREATE OR REPLACE FUNCTION sd(vals float8[]) RETURNS float AS '
    sd(vals)
' LANGUAGE 'plr' STRICT;

http://www.joeconway.com/plr/doc/plr-funcs.html

Something else in your code gives me the fear though. Remember, this R code is being called by the Postgres server while executing queries, and you are connecting to, presumably the same Postgres server and executing another query. This looks like a dozen shades of wrong.

The right way to access the DB in PL/R is via the routines outlined in the 'Normal Support' section:

http://www.joeconway.com/plr/doc/plr-spi-rsupport-funcs-normal.html

There are some 'compatibility support' functions that are like the RPostgreSQL package functions:

http://www.joeconway.com/plr/doc/plr-spi-rsupport-funcs-compat.html

but if you require(RPostgreSQL) in your PL/R code you'll probably mask them and your database will disappear up a black hole. Use the 'normal support' routines in PL/R functions.