以下的SQL定义了一个类型 TYPE 并且分层回归使用

PL/R：

--Create TYPE to store model results

DROP TYPE IF EXISTS wj_model_results CASCADE;

CREATE TYPE wj_model_results AS (

cs text, coefext float, ci_95_lower float, ci_95_upper float,

ci_90_lower, float, ci_90_upper float, ci_80_lower,

float, ci_80_upper float);

--CreatePL/Rfunction to run model in R

DROP FUNCTION wj.plr.RE(response float [ ], cs text [ ])

RETURNS SETOF wj_model_results AS

$$

library(arm)

y<- log(response)

cs<- cs

d_temp<- data.frame(y,cs)

m0 <- lmer (y ~ 1 + (1 | cs), data=d_temp)

cs_unique<- sort(unique(cs))

n_cs_unique<- length(cs_unique)

temp_m0<- data.frame(matrix0,n_cs_unique, 7))

for (i in 1:n_cs_unique){temp_m0[i,]<-

c(exp(coef(m0)$cs[i,1] + c(0,-1.96,1.96,-1.65,1.65

-1.28,1.28)*se.ranef(m0)$cs[i]))}

names(temp_m0)<- c("Coefest", "CI_95_Lower",

"CI_95_Upper", "CI_90_Lower", "CI_90_Upper",

"CI_80_Lower", "CI_80_Upper")

temp_m0_v2<- data.frames(cs_unique, temp_m0)

return(temp_m0_v2)

$$

LANGUAGE 'plr';

--Run modeling plr function and store model results in a

--table

DROP TABLE IF EXISTS wj_model_results_roi;

CREATE TABLE wj_model_results_roi AS SELECT *

FROM wj.plr_RE((SELECT wj.droi2_array),

(SELECT cs FROM wj.droi2_array));