pllua

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

CREATE EXTENSION pllua;

CREATE EXTENSION pllua;

CREATE FUNCTION lua_max(a integer, b integer) RETURNS integer LANGUAGE pllua AS $$
  if a > b then return a else return b end
$$;

CREATE FUNCTION hello(name text) RETURNS text LANGUAGE pllua AS $$
  return "Hello, " .. name .. "!"
$$;

pgtype.numeric(1234)
pgtype.date('2017-12-01')
pgtype.array.integer(1, 2, 3, 4)
pgtype.numrange(1, 2)

row.columnname     -- 按名称访问
row[3]             -- 按属性编号访问
for colname, value, attnum in pairs(row) do ... end

CREATE FUNCTION generate_n(n integer) RETURNS SETOF integer LANGUAGE pllua AS $$
  for i = 1, n do
    coroutine.yield(i)
  end
$$;

-- 简单查询
local rows = spi.execute("SELECT * FROM mytable WHERE id = $1", 42)

-- 行迭代器
for row in spi.rows("SELECT * FROM mytable") do
  print(row.name)
end

-- 预备语句
local stmt = spi.prepare("SELECT * FROM users WHERE id = $1", {'integer'})
local result = stmt:execute(42)
for row in stmt:rows(42) do ... end

local cursor = spi.newcursor()
cursor:open("SELECT * FROM items")
local rows = cursor:fetch(10)
cursor:move(5)
cursor:close()

CREATE FUNCTION my_trigger() RETURNS trigger LANGUAGE pllua AS $$
  function(trigger, old, new)
    trigger.row = new
    return trigger.row
  end
$$;

spi.error('division_by_zero', 'Cannot divide by zero')
spi.notice('informational message')
spi.warning('warning message')

-- 使用 pcall 进行子事务
local ok, err = pcall(function()
  spi.execute("INSERT INTO mytable VALUES ($1)", val)
end)

print("info message")
spi.debug("debug")
spi.notice("notice")
spi.warning("warning")
spi.error("error")