0

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.

SunWuKung
  • 527
  • 4
  • 16

1 Answers1

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();
anwjones
  • 11
  • 1
  • Thanks for the answer. I know I need to do that when I am returning a set of records. Here though I am looking for a function to write back the data as a table. The main reason for that is that I will not know in advance the column names (since the data is the result of a cast). – SunWuKung Jan 24 '17 at 09:02