how to write dataframe as table in the db in plr

Question

I would like to write back a dataframe to the db in plr.

In standard R I could just do:

require("RPostgreSQL")
con <-  dbConnect(dbDriver("PostgreSQL"), dbname = , port = ,  user = )
data(iris)
dbWriteTable(con, 'iris', iris, row.names=FALSE)

In plr however I am already connected to the database. I looked at the plr documentation here: http://www.joeconway.com/plr/doc/plr-US.pdf but couldn't find an example, also found this sqlshorthands.R document but the example there didnt work for me.

Answer 1

--PostgreSQL always needs to know the function return type!
create or replace function r_iris(
    OUT "Sepal.Length" float8,
    OUT "Sepal.Width" float8, 
    OUT "Petal.Length" float8, 
    OUT "Petal.Width" float8, 
    OUT "Species" text)
returns setof record
As 
$$
data('iris')
iris
$$ LANGUAGE plr;

--assume there is an existing schema called testing
Select * into testing.iris From r_iris();