PL / R function that takes two tables as arguments

Question

I am trying to find examples of a PL / R function that can accept two postgres tables. The PL / R docs do not provide such an example.
To have working examples, consider using the merge of two postgres tables on the R side.

Having two tables in postgres

CREATE TABLE x (a numeric, b text); CREATE TABLE y (a numeric, d text); INSERT INTO x VALUES (1, 'a'),(2, 'b'); INSERT INTO y VALUES (2, 'b'),(3, 'c'); 

I want to replace the following query

 SELECT * FROM x INNER JOIN y ON xa=ya; 

With the PL / R function defined in R as:

 my_function = function(x, y){ merge(x, y, by = "a") } 

I managed to call the PL / R function, which takes one table, but not two.

Answer 1

I don't think postgeql can take a real table as parameters.

But there is another way to do this. You can pass table names as parameters.
Here is the function

 CREATE OR REPLACE FUNCTION merge(t1 text, t2 text) returns setof x as BEGIN execute 'select * from ' || t1 ||' join ' || t2 || ' on t1.a=t2.a'; END 

The postgresql function was above, it can also be written in R-functions.
Here is the code for R

• We must store all the values ​​of table x in the variable x. See Codes below

  x <- dbGetQuery(con, "SELECT * from sandbox.x") --con is the connection that connects to your db, the sandbox is the name of the schema, x is the name of the table

• Store y table values ​​in y variable

  y<-dbGetQuery(con, "SELECT * from sandbox.y")

• join 2 tables

  total <- merge(x,y,by="a")

• You can also write another function to wrap the merge function, see codes below

  myTotal <- function(x,y) { result <- merge(x,y,by="a") return(result) }

I have attached a screenshot of the steps for your reference