plr

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION plr;

CREATE EXTENSION plr;

CREATE OR REPLACE FUNCTION r_max(integer, integer) RETURNS integer AS '
if (arg1 > arg2)
  return(arg1)
else
  return(arg2)
' LANGUAGE plr STRICT;

SELECT r_max(10, 20);  -- 20

CREATE OR REPLACE FUNCTION sd(vals float8[]) RETURNS float AS '
sd(vals)
' LANGUAGE plr STRICT;

SELECT sd(ARRAY[1.0, 2.0, 3.0, 4.0, 5.0]);

CREATE OR REPLACE FUNCTION r_max(integer, integer) RETURNS integer AS '
if (is.null(arg1) && is.null(arg2))
  return(NULL)
if (is.null(arg1))
  return(arg2)
if (is.null(arg2))
  return(arg1)
if (arg1 > arg2)
  return(arg1)
return(arg2)
' LANGUAGE plr;

CREATE OR REPLACE FUNCTION test_spi(text) RETURNS SETOF record AS '
pg.spi.exec(arg1)
' LANGUAGE plr;

SELECT * FROM test_spi('SELECT oid, typname FROM pg_type LIMIT 5')
  AS t(oid oid, typname name);

SELECT load_r_typenames();

CREATE OR REPLACE FUNCTION lookup_type(type_name text)
RETURNS SETOF record AS $$
sp <- pg.spi.prepare(
  'SELECT oid, typname FROM pg_type WHERE typname = $1',
  c(NAMEOID)
)
pg.spi.execp(sp, list(type_name))
$$ LANGUAGE plr;

SELECT * FROM lookup_type('text') AS t(oid oid, typname name);

CREATE OR REPLACE FUNCTION get_numbers(n int) RETURNS SETOF integer AS '
1:n
' LANGUAGE plr;

SELECT * FROM get_numbers(5);

CREATE OR REPLACE FUNCTION r_regr_slope(float8, float8, int)
RETURNS float8 AS '
slope <- NA
y <- farg1
x <- farg2
if (fnumrows == arg3 + 1L)
  try(slope <- lm(y ~ x)$coefficients[2])
return(slope)
' LANGUAGE plr WINDOW;

CREATE OR REPLACE FUNCTION set_state(key text, val text) RETURNS void AS '
assign(key, val, env=.GlobalEnv)
' LANGUAGE plr;

SELECT load_r_typenames();  -- Load type OID variables
SELECT * FROM r_typenames(); -- List available type OIDs
SELECT plr_version();        -- PL/R version