--
\set VERBOSITY terse
set pllua.on_common_init = 'require "pllua.compat"';
-- tests taken from old pllua
-- minimal function
CREATE FUNCTION hello(name text)
RETURNS text AS $$
  return string.format("Hello, %s!", name)
$$ LANGUAGE pllua;
SELECT hello('PostgreSQL');
       hello        
--------------------
 Hello, PostgreSQL!
(1 row)

-- null handling
CREATE FUNCTION max(a integer, b integer) RETURNS integer AS $$
  if a == nil then return b end -- first arg is NULL?
  if b == nil then return a end -- second arg is NULL?
  return a > b and a or b -- return max(a, b)
$$ LANGUAGE pllua;
SELECT max(1,2), max(2,1), max(2,null), max(null, 2), max(null, null);
 max | max | max | max | max 
-----+-----+-----+-----+-----
   2 |   2 |   2 |   2 |    
(1 row)

-- plain recursive
CREATE FUNCTION fib(n int) RETURNS int AS $$
  if n < 3 then
    return n
  else
    return fib(n - 1) + fib(n - 2)
  end
$$ LANGUAGE pllua;
SELECT fib(4);
 fib 
-----
   5
(1 row)

-- memoized
CREATE FUNCTION fibm(n integer) RETURNS integer AS $$
  if n < 3 then return n
  else
    local v = _U[n]
    if not v then
      v = fibm(n - 1) + fibm(n - 2)
      _U[n] = v
    end
    return v
  end
end
do _U = {}
$$ LANGUAGE pllua;
SELECT fibm(4);
 fibm 
------
    5
(1 row)

-- tail recursive
CREATE FUNCTION fibt(n integer) RETURNS integer AS $$
  return _U(n, 0, 1)
end
_U = function(n, a, b)
  if n < 1 then return b
  else return _U(n - 1, b, a + b) end
$$ LANGUAGE pllua;
SELECT fibt(4);
 fibt 
------
    5
(1 row)

-- iterator
CREATE FUNCTION fibi() RETURNS integer AS $$
  while true do
    _U.curr, _U.next = _U.next, _U.curr + _U.next
    coroutine.yield(_U.curr)
  end
end
do
  _U = {curr = 0, next = 1}
  fibi = coroutine.wrap(fibi)
$$ LANGUAGE pllua;
SELECT fibi(), fibi(), fibi(), fibi(), fibi();
 fibi | fibi | fibi | fibi | fibi 
------+------+------+------+------
    1 |    1 |    2 |    3 |    5
(1 row)

SELECT fibi(), fibi(), fibi(), fibi(), fibi();
 fibi | fibi | fibi | fibi | fibi 
------+------+------+------+------
    8 |   13 |   21 |   34 |   55
(1 row)

-- upvalue
CREATE FUNCTION counter() RETURNS int AS $$
  while true do
    _U = _U + 1
    coroutine.yield(_U)
  end
end
do
  _U = 0 -- counter
  counter = coroutine.wrap(counter)
$$ LANGUAGE pllua;
SELECT counter();
 counter 
---------
       1
(1 row)

SELECT counter();
 counter 
---------
       2
(1 row)

SELECT counter();
 counter 
---------
       3
(1 row)

-- record input
CREATE TYPE greeting AS (how text, who text);
CREATE FUNCTION makegreeting (g greeting, f text) RETURNS text AS $$
  return string.format(f, g.how, g.who)
$$ LANGUAGE pllua;
SELECT makegreeting(('how', 'who'), '%s, %s!');
 makegreeting 
--------------
 how, who!
(1 row)

-- array, record output
CREATE FUNCTION greetingset (how text, who text[])
RETURNS SETOF greeting AS $$
  for _, name in ipairs(who) do
    coroutine.yield{how=how, who=name}
  end
$$ LANGUAGE pllua;
SELECT makegreeting(greetingset, '%s, %s!') FROM
  (SELECT greetingset('Hello', ARRAY['foo', 'bar', 'psql'])) AS q;
 makegreeting 
--------------
 Hello, foo!
 Hello, bar!
 Hello, psql!
(3 rows)

-- more array, upvalue
CREATE FUNCTION perm (a text[]) RETURNS SETOF text[] AS $$
  _U(a, #a)
end
do
  _U = function (a, n) -- permgen in PiL
    if n == 0 then
      coroutine.yield(a) -- return next SRF row
    else
      for i = 1, n do
        a[n], a[i] = a[i], a[n] -- i-th element as last one
        _U(a, n - 1) -- recurse on head
        a[n], a[i] = a[i], a[n] -- restore i-th element
      end
    end
  end
$$ LANGUAGE pllua;
SELECT * FROM perm(array['1', '2', '3']);
  perm   
---------
 {2,3,1}
 {3,2,1}
 {3,1,2}
 {1,3,2}
 {2,1,3}
 {1,2,3}
(6 rows)

-- shared variables
CREATE FUNCTION getcounter() RETURNS integer AS $$
  if shared.counter == nil then -- not cached?
    setshared("counter", 0)
  end
  return counter -- _G.counter == shared.counter
$$ LANGUAGE pllua;
CREATE FUNCTION setcounter(c integer) RETURNS void AS $$
  if shared.counter == nil then -- not cached?
    setshared("counter", c)
  else
    counter = c -- _G.counter == shared.counter
  end
$$ LANGUAGE pllua;
SELECT getcounter();
 getcounter 
------------
          0
(1 row)

SELECT setcounter(5);
 setcounter 
------------
 
(1 row)

SELECT getcounter();
 getcounter 
------------
          5
(1 row)

-- SPI usage
CREATE TABLE sometable ( sid int4, sname text, sdata text);
INSERT INTO sometable VALUES (1, 'name', 'data');
CREATE FUNCTION get_rows (i_name text) RETURNS SETOF sometable AS $$
  if _U == nil then -- plan not cached?
    local cmd = "SELECT sid, sname, sdata FROM sometable WHERE sname = $1"
    _U = server.prepare(cmd, {"text"}):save()
  end
  local c = _U:getcursor({i_name}, true) -- read-only
  while true do
    local r = c:fetch(1)
    if r == nil then break end
    r = r[1]
    coroutine.yield{sid=r.sid, sname=r.sname, sdata=r.sdata}
  end
  c:close()
$$ LANGUAGE pllua;
SELECT * FROM get_rows('name');
 sid | sname | sdata 
-----+-------+-------
   1 | name  | data
(1 row)

SET client_min_messages = warning;
CREATE TABLE tree (id INT PRIMARY KEY, lchild INT, rchild INT);
RESET client_min_messages;
CREATE FUNCTION filltree (t text, n int) RETURNS void AS $$
  local p = server.prepare("insert into " .. t .. " values($1, $2, $3)",
    {"int4", "int4", "int4"})
  for i = 1, n do
    local lchild, rchild = 2 * i, 2 * i + 1 -- siblings
    p:execute{i, lchild, rchild} -- insert values
  end
$$ LANGUAGE pllua;
SELECT filltree('tree', 10);
 filltree 
----------
 
(1 row)

CREATE FUNCTION preorder (t text, s int) RETURNS SETOF int AS $$
  coroutine.yield(s)
  local q = server.execute("select * from " .. t .. " where id=" .. s,
      true, 1) -- read-only, only 1 result
  if q ~= nil then
    local lchild, rchild = q[1].lchild, q[1].rchild -- store before next query
    if lchild ~= nil then preorder(t, lchild) end
    if rchild ~= nil then preorder(t, rchild) end
  end
$$ LANGUAGE pllua;
SELECT * from preorder('tree', 1);
 preorder 
----------
        1
        2
        4
        8
       16
       17
        9
       18
       19
        5
       10
       20
       21
       11
        3
        6
       12
       13
        7
       14
       15
(21 rows)

CREATE FUNCTION postorder (t text, s int) RETURNS SETOF int AS $$
  local p = _U[t]
  if p == nil then -- plan not cached?
    p = server.prepare("select * from " .. t .. " where id=$1", {"int4"})
    _U[t] = p:save()
  end
  local c = p:getcursor({s}, true) -- read-only
  local q = c:fetch(1) -- one row
  if q ~= nil then
    local lchild, rchild = q[1].lchild, q[1].rchild -- store before next query
    c:close()
    if lchild ~= nil then postorder(t, lchild) end
    if rchild ~= nil then postorder(t, rchild) end
  end
  coroutine.yield(s)
end
do _U = {} -- plan cache
$$ LANGUAGE pllua;
SELECT * FROM postorder('tree', 1);
 postorder 
-----------
        16
        17
         8
        18
        19
         9
         4
        20
        21
        10
        11
         5
         2
        12
        13
         6
        14
        15
         7
         3
         1
(21 rows)

-- trigger
CREATE FUNCTION treetrigger() RETURNS trigger AS $$
  local row, operation = trigger.row, trigger.operation
  if operation == "update" then
    trigger.row = nil -- updates not allowed
  elseif operation == "insert" then
    local id, lchild, rchild = row.id, row.lchild, row.rchild
    if lchild == rchild or id == lchild or id == rchild -- avoid loops
        or (lchild ~= nil and _U.intree(lchild)) -- avoid cycles
        or (rchild ~= nil and _U.intree(rchild))
        or (_U.nonemptytree() and not _U.isleaf(id)) -- not leaf?
        then
      trigger.row = nil -- skip operation
    end
  else -- operation == "delete"
    if not _U.isleafparent(row.id) then -- not both leaf parent?
      trigger.row = nil
    end
  end
end
do
  local getter = function(cmd, ...)
    local plan = server.prepare(cmd, {...}):save()
    return function(...)
      return plan:execute({...}, true) ~= nil
    end
  end
  _U = { -- plan closures
    nonemptytree = getter("select * from tree"),
    intree = getter("select node from (select id as node from tree "
      .. "union select lchild from tree union select rchild from tree) as q "
      .. "where node=$1", "int4"),
    isleaf = getter("select leaf from (select lchild as leaf from tree "
      .. "union select rchild from tree except select id from tree) as q "
      .. "where leaf=$1", "int4"),
    isleafparent = getter("select lp from (select id as lp from tree "
      .. "except select ti.id from tree ti join tree tl on ti.lchild=tl.id "
      .. "join tree tr on ti.rchild=tr.id) as q where lp=$1", "int4")
  }
$$ LANGUAGE pllua;
CREATE TRIGGER tree_trigger BEFORE INSERT OR UPDATE OR DELETE ON tree
  FOR EACH ROW EXECUTE PROCEDURE treetrigger();
SELECT * FROM tree WHERE id = 1;
 id | lchild | rchild 
----+--------+--------
  1 |      2 |      3
(1 row)

UPDATE tree SET rchild = 1 WHERE id = 1;
SELECT * FROM tree WHERE id = 10;
 id | lchild | rchild 
----+--------+--------
 10 |     20 |     21
(1 row)

DELETE FROM tree where id = 10;
DELETE FROM tree where id = 1;
-- passthru types
CREATE FUNCTION echo_int2(arg int2) RETURNS int2 AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_int2('12345');
 echo_int2 
-----------
     12345
(1 row)

CREATE FUNCTION echo_int4(arg int4) RETURNS int4 AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_int4('1234567890');
 echo_int4  
------------
 1234567890
(1 row)

CREATE FUNCTION echo_int8(arg int8) RETURNS int8 AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_int8('1234567890');
 echo_int8  
------------
 1234567890
(1 row)

SELECT echo_int8('12345678901236789');
     echo_int8     
-------------------
 12345678901236789
(1 row)

SELECT echo_int8('1234567890123456789');
      echo_int8      
---------------------
 1234567890123456789
(1 row)

CREATE FUNCTION echo_text(arg text) RETURNS text AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_text('qwe''qwe');
 echo_text 
-----------
 qwe'qwe
(1 row)

CREATE FUNCTION echo_bytea(arg bytea) RETURNS bytea AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_bytea('qwe''qwe');
    echo_bytea    
------------------
 \x71776527717765
(1 row)

SELECT echo_bytea(E'q\\000w\\001e''q\\\\we');
       echo_bytea       
------------------------
 \x710077016527715c7765
(1 row)

CREATE FUNCTION echo_timestamptz(arg timestamptz) RETURNS timestamptz AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_timestamptz('2007-01-06 11:11 UTC') AT TIME ZONE 'UTC';
         timezone         
--------------------------
 Sat Jan 06 11:11:00 2007
(1 row)

CREATE FUNCTION echo_timestamp(arg timestamp) RETURNS timestamp AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_timestamp('2007-01-06 11:11');
      echo_timestamp      
--------------------------
 Sat Jan 06 11:11:00 2007
(1 row)

CREATE FUNCTION echo_date(arg date) RETURNS date AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_date('2007-01-06');
 echo_date  
------------
 01-06-2007
(1 row)

CREATE FUNCTION echo_time(arg time) RETURNS time AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_time('11:11');
 echo_time 
-----------
 11:11:00
(1 row)

CREATE FUNCTION echo_arr(arg text[]) RETURNS text[] AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_arr(array['a', 'b', 'c']);
 echo_arr 
----------
 {a,b,c}
(1 row)

CREATE DOMAIN mynum AS numeric(6,3);
CREATE FUNCTION echo_mynum(arg mynum) RETURNS mynum AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_mynum(666.777);
 echo_mynum 
------------
    666.777
(1 row)

CREATE TYPE mytype AS (id int2, val mynum, val_list numeric[]);
CREATE FUNCTION echo_mytype(arg mytype) RETURNS mytype AS $$ return arg $$ LANGUAGE pllua;
SELECT echo_mytype((1::int2, 666.777, array[1.0, 2.0]) );
       echo_mytype       
-------------------------
 (1,666.777,"{1.0,2.0}")
(1 row)

CREATE FUNCTION nested_server_rows () RETURNS SETOF text as
$$
for left in server.rows('select generate_series as left from generate_series(3,4) ') do
for right in server.rows('select generate_series as right from generate_series(5,6) ') do
	local s = left.left.." "..right.right
	coroutine.yield(s)
end
end
$$
language pllua;
select nested_server_rows();
 nested_server_rows 
--------------------
 3 5
 3 6
 4 5
 4 6
(4 rows)

CREATE OR REPLACE FUNCTION pg_temp.srf()
RETURNS SETOF integer AS $$
  coroutine.yield(1)
  coroutine.yield(nil)
  coroutine.yield(2)
$$ LANGUAGE pllua;
select quote_nullable(pg_temp.srf());
 quote_nullable 
----------------
 '1'
 NULL
 '2'
(3 rows)

CREATE OR REPLACE FUNCTION pg_temp.srf()
RETURNS SETOF integer AS $$
  coroutine.yield(1)
  coroutine.yield()
  coroutine.yield(2)
$$ LANGUAGE pllua;
select quote_nullable(pg_temp.srf());
 quote_nullable 
----------------
 '1'
 NULL
 '2'
(3 rows)

CREATE or replace FUNCTION pg_temp.inoutf(a integer, INOUT b text, INOUT c text)  AS
$$
begin
c = a||'c:'||c;
b = 'b:'||b;
end
$$
LANGUAGE plpgsql;
do $$
local a = server.execute("SELECT pg_temp.inoutf(5, 'ABC', 'd') as val ");
local r = a[1].val
print(r.b)
print(r.c)
$$ language pllua;
INFO:  b:ABC
INFO:  5c:d
-- body reload
SELECT hello('PostgreSQL');
       hello        
--------------------
 Hello, PostgreSQL!
(1 row)

CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text AS $$
  return string.format("Bye, %s!", name)
$$ LANGUAGE pllua;
SELECT hello('PostgreSQL');
      hello       
------------------
 Bye, PostgreSQL!
(1 row)