Push R код в PL/R код в базе данных postgresql

Question

Я пытаюсь преобразовать успешный код R с использованием Rpostgresql в код PL/R, чтобы избежать выталкивания / извлечения данных из базы данных postgreql и из нее.

Код - это dcast для data.table:

#libs
library(RPostgreSQL);
library(data.table);

# connect
drv <- dbDriver("PostgreSQL");
con <- dbConnect(drv, dbname="postgres", user="postgres");

# load
cli_ranges <- dbGetQuery(con, "SELECT custid, prod_ranges, is_cli from cli_ranges;")

# DT 
setDT(cli_ranges )
setkeyv(cli_ranges , c("prod_ranges"))

# pivot
cli_ranges.pivoted <- dcast(cli_ranges, custid ~ paste0("is_cli_", prod_ranges), fun=sum, value.var = "is_cli")

# send back to DB
dbWriteTable(con, "cli_ranges_pivoted", cli_ranges.pivoted, row.names=F)

Код в R работает отлично и быстро.

Я сейчас пытаюсь вставить код в функцию PL/R,

CREATE OR REPLACE FUNCTION public.pivot()
  RETURNS void AS
$BODY$

[copy/paste R code]

$BODY$
  LANGUAGE plr;

... но последняя строка кода R (dbWriteTable) бросает:

ERROR:  R interpreter expression evaluation error
DETAIL:  Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function 'dbWriteTable' for signature '"logical", "character", "data.frame"'
CONTEXT:  In PL/R function pivot

Изменение data.table на dataframe (as.data.frame(cli_ranges.pivoted)) тоже не работает.

Один трюк может состоять в том, чтобы вернуть data.table / frame для выполнения CREATE TABLE cli_ranges_pivoted AS SELECT pivot(); но я не знаю, как подтолкнуть data.frame в качестве вывода...

cli_ranges Таблица:

custid  prod_ranges is_cli
1       A           1
1       B           1
1       C           0
2       A           1
2       B           0
2       C           1
3       A           0
3       B           1
3       C           0
4       A           1
...     ...         ...

После dcast (т.е. поворот) датафрейм выглядит следующим образом:

custid  prod_ranges_A   prod_ranges_B   prod_ranges_C
1       1               1               0
2       1               0               1
3       0               1               0
4       1               ...
...

Количество различных значений в prod_ranges часто меняется, поэтому я могу определить количество столбцов после поворота.

Конверт: Postgresql 9.5, R 3.3, PL/R 08.03.00.16, Win 10 64bit

Answer 1

Вы можете начать с http://gpdb.docs.pivotal.io/4330/ref_guide/pl_r.html Примеры 2 и 3.

Или, вы можете попробовать изменить функцию unnest, как это сделали Лукас Эклунд и Эрвинд Брандштеттер в этом посте (я использую решение Лукаса): Развернуть массив на один уровень.

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray)
RETURNS SETOF anyarray AS
$BODY$
  SELECT array_agg($1[series2.i][series2.x]) FROM
    (SELECT generate_series(array_lower($1,2),array_upper($1,2)) as x, series1.i
     FROM 
     (SELECT generate_series(array_lower($1,1),array_upper($1,1)) as i) series1 
    ) series2
GROUP BY series2.i
$BODY$
LANGUAGE sql IMMUTABLE;

и тогда вы можете попытаться вернуть массив и сделать что-то вроде этого:

CREATE OR REPLACE FUNCTION r_norm(n integer, mean float8, std_dev float8)
 RETURNS float8[] 
AS $$
 x<-rnorm(n,mean,std_dev); 
 y<-rnorm(n,mean,std_dev);
 final<-cbind(as.data.frame(x), as.data.frame(y));
return(final) 

$$ LANGUAGE 'plr';

CREATE TABLE test_norm_var AS SELECT R_output[1] as col1, R_output[2] as col2 FROM unnest_multidim(r_norm(10,0,1)) R_output;

SELECT col1 FROM test_norm_var;

РЕДАКТИРОВАТЬ

Я не мог заставить dbWriteTable работать так, как он задумывался как функция PL/R... НО, вы также можете попробовать этот метод

CREATE OR REPLACE FUNCTION pivot() 
  RETURNS VOID as $$

library(RPostgreSQL);
library(data.table);

drv <- dbDriver("PostgreSQL");
con <- dbConnect(drv, dbname ="postgres");

fields <- list(custid = "numeric",prod_ranges = "varchar(128)", is_cli = "numeric")

custid <- c(1,1,1,2,2,2)
prod_ranges <- c("A","B","C","A","B","C")
is_cli <- c(1,1,0,1,0,1)

cli_ranges <- data.frame(custid,prod_ranges,is_cli, stringsAsFactors = default.stringsAsFactors())

setDT(cli_ranges )
setkeyv(cli_ranges , c("prod_ranges"))

cli_ranges.pivoted <- as.data.frame(dcast(cli_ranges, custid ~ paste0("is_cli_", prod_ranges), fun=sum, value.var = "is_cli"))

create_query <- paste0("CREATE TABLE cli_ranges (",paste0(colnames(cli_ranges.pivoted), collapse = " numeric, "),
" numeric) DISTRIBUTED BY (",colnames(cli_ranges)[1],")")

dbGetQuery(con, create_query);

values_string <- "("
for ( i in 1:dim(cli_ranges.pivoted)[1]){
 for ( j in 1:dim(cli_ranges.pivoted)[2] ){
    if ( j != dim(cli_ranges.pivoted)[2]) { 
        values_string <- paste0(values_string,cli_ranges.pivoted[i,j],",")
    } else { 
        values_string <- paste0(values_string,cli_ranges.pivoted[i,j])
    }
  } 
    if ( i != dim(cli_ranges.pivoted)[1] ){
        values_string <- paste0(values_string,"),(")
    } else {
        values_string <- paste0(values_string,")")
    }
}

insert_query <- paste0("INSERT INTO cli_ranges (",paste0(colnames(cli_ranges.pivoted), collapse = ", "),
") VALUES ", values_string )

dbGetQuery(con, insert_query);

$$ LANGUAGE plr;