PL/R function accepting two tables as arguments

Question

I'm struggling to find an examples of a PL/R function that could accepts two postgres tables. PL/R docs does not provide any such example.
To have a working examples lets considering using merge of two postgres tables on 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'm looking to replace following query

SELECT * FROM x INNER JOIN y ON x.a=y.a;

With the PL/R function defined in R as:

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

I was able to call PL/R function which accepts single table, but not two.

Answer 1

I don't think that postgeql can accept real table as parameters.

But there is another way to do it. You can pass the 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 above is the function for postgresql, it can also written in the R function.
Here is the code for R

1. We have to store all the values of table x into a variable x. See the codes below

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

2. Store values of table y into a variable y

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

3. merge the 2 tables

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

4. You can also write another function to wrapper the merge function, see the codes below

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

I attached a screenshot of the steps for your reference