1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
PL/Lua Introduction
===================
PL/Lua is a procedural language module for the PostgreSQL database
that allows server-side functions to be written in Lua.
Quick start
-----------
create extension pllua;
create function hello(person text) returns text language pllua as $$
return "Hello, " .. person .. ", from Lua!"
$$;
select hello('Fred');
hello
------------------------
Hello, Fred, from Lua!
(1 row)
Basic Examples
--------------
### Using `print()` for interactive diagnostics
Anything passed to the `print()` function will be raised as a
notification at `INFO` level, causing `psql` to display it
interactively; program clients will usually just ignore non-error
notices.
create function print_lua_ver() returns void language pllua as $$
print(_VERSION)
$$;
select print_lua_ver();
INFO: Lua 5.3
print_lua_ver
---------------
(1 row)
### Simple arguments and results
Simple scalar types (integers, floats, text, bytea, boolean) are
converted to the matching Lua type, and conversely for results.
create function add2(a integer, b integer) returns integer language pllua
as $$
return a + b
$$;
Other data types are passed as userdata objects that can be converted
to strings with `tostring()` or accessed via provided methods and
metamethods. In particular, arrays and records are accessible in most
ways as though they were Lua tables, though they're actually not.
create type myrow as (a integer, b text[]);
create function foo(rec myrow) returns myrow language pllua as $$
print("a is", rec.a)
print("b[1] is", rec.b[1])
print("b[2] is", rec.b[2])
return { a = 123, b = {"fred","jim"} }
$$;
select * from foo(row(1,array['foo','bar'])::myrow);
INFO: a is 1
INFO: b[1] is foo
INFO: b[2] is bar
a | b
-----+------------
123 | {fred,jim}
(1 row)
### Sum of an array
create function array_sum(a integer[]) returns integer language pllua
as $$
local total = 0
for k,v in pairs(a) do
total = total + v
end
return total
$$;
The above assume single-dimension arrays with no NULLs. A more generic
method uses the array mapping function provided by the array userdata:
create function array_sum(a integer[]) returns integer language pllua
as $$
local total = 0
a{ null = 0,
map = function(v,...) total = total + v end,
discard = true }
return total
$$;
### Simple database queries
The local environment created for each function is a good place to
cache prepared queries:
create table objects (id integer primary key, value text);
create function get_value(id integer) returns text language pllua stable
as $$
local r = q:execute(id)
return r and r[1] and r[1].value or 'value not found'
end
do -- the part below will be executed once before the first call
q = spi.prepare("select value from objects where id=$1")
$$;
The result of executing a query is a table containing rows (if any)
for select queries, or an integer rowcount for queries that do not
return rows.
### Triggers
create function mytrigger() returns trigger language pllua
as $$
-- trigger functions are implicitly declared f(trigger,old,new,...)
new.total_cost = new.price * new.qty;
return new
$$;
### JSON handling
Values of type `json` are passed to Lua simply as strings. But the
`jsonb` data type is supported in a more direct fashion.
`jsonb` values can be mapped to Lua tables in a configurable way, and
Lua tables converted back to `jsonb` values:
create function add_stuff(val jsonb) returns jsonb language pllua
as $$
local t = val{} -- convert jsonb to table with default settings
t.newkey = { { foo = 1 }, { bar = 2 } }
return t
$$;
select add_stuff('{"oldkey":123}');
add_stuff
-----------------------------------------------------
{"newkey": [{"foo": 1}, {"bar": 2}], "oldkey": 123}
(1 row)
The above simplistic approach will tend to drop json null values
(since Lua does not store nulls in tables), and loses precision on
numeric values not representable as floats; this can be avoided as
follows:
create function add_stuff(val jsonb) returns jsonb language pllua
as $$
local nullval = {} -- use some unique object to mark nulls
local t = val{ null = nullval, pg_numeric = true }
t.newkey = { { foo = 1 }, { bar = 2 } }
return t, { null = nullval }
$$;
select add_stuff('{"oldkey":[147573952589676412928,null]}');
add_stuff
-------------------------------------------------------------------------------
{"newkey": [{"foo": 1}, {"bar": 2}], "oldkey": [147573952589676412928, null]}
(1 row)
Tables that originated from JSON are tagged as to whether they were
originally objects or arrays, so as long as you provide a unique null
value, this form of round-trip conversion should not change anything.
(See the `pllua.jsonb` module documentation for more detail.)
<!--eof-->
|
