plv8x helps you manage functions and packages in plv8, postgresql's javascript procedural language support.
Note: Requires postgresql 9.0 or later.
postgresql PGDG apt respository now ships plv8js extension:
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.2-plv8
Or you can install with pgxnclient:
sudo easy_install pgxnclient
sudo pgxn install plv8
% npm i -g LiveScript
% git clone git://github.com/clkao/plv8x.git; cd plv8x
% npm i -g .
Enable plv8x for your database:
% createdb test
% plv8x -d test -l
plv8x: 491425 bytes
We support synonymous PLV8XDB
and PLV8XCONN
environment variables,
so there's no need to type -d
over and over again on the command line:
% export PLV8XDB=test
Now create some test data with json columns: (example table from Postgres 9.3 feature highlight: JSON operators)
% psql test
test=# CREATE TABLE aa (a int, b json);
CREATE TABLE
test=# INSERT INTO aa VALUES (1, '{"f1":1,"f2":true,"f3":"Hi I''m \"Daisy\""}');
INSERT 0 1
test=# INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');
INSERT 0 1
test=# INSERT INTO aa VALUES (3, '{"f1":[1,"Robert \"M\"",true],"f2":[2,"Kevin \"K\"",false]}');
INSERT 0 1
Instead of b->'f1'
, we use b~>'this.f1'
, which means bind b
as this
and evaluate the right hand side (this.f1
):
test=# SELECT b~>'this.f1' AS f1, b~>'this.f3' AS f3 FROM aa WHERE a = 1;
f1 | f3
----+--------------------
1 | "Hi I'm \"Daisy\""
If you like coffee, @
works too:
test=# SELECT b~>'@f1' AS f1, b~>'@f3' AS f3 FROM aa WHERE a = 1;
f1 | f3
----+--------------------
1 | "Hi I'm \"Daisy\""
For multiple keys, you can of course do b~>'@f1'~>'@f12'
, but single expression will do:
test=# SELECT b~>'@f1'~>'@f12' as f12_long, b~>'@f1.f12' AS f12 FROM aa WHERE a = 2;
f12_long | f12
----------+-----
12 | 12
Ditto for arrays:
postgres=# SELECT b~>'@f1[0]' as f1_0 FROM aa WHERE a = 3;
f1_0
------
1
Unary ~>
for just evaluating the expression:
test=# SELECT ~>'[1 to 10]' AS f1
f1
------------------------
[1,2,3,4,5,6,7,8,9,10]
~>
is actually a shorthand for |> '~>...'
. Using raw |>
for plain
old javascript:
test=# select '{"foo": [1,2,3]}'::json |> 'function() { return this.foo[1] }';
?column?
----------
2
Expression works too:
test=# select '{"foo": [1,2,3]}'::json |> 'return this.foo[1]';
?column?
----------
2
CoffeeScript:
test=# select '{"foo": [1,2,3]}'::json |> '@foo[1]';
?column?
----------
2
<|
is |>
reversed:
test=# select '@foo.1 * 5' <| '{"foo": [1,2,3]}'::json
?column?
----------
10
|>
as unary operator:
test=# select |> '~> plv8x.require "LiveScript" .compile "-> \Hello" {+bare}';
?column?
--------------------------------------
"(function(){\n return Hello;\n});"
Let's try reusing some existing npm modules:
% npm i -g qs
% plv8x -i qs # same as: plv8x -i qs:/path/to/qs/package.json
% psql test
# parse a query string
test=# select ~>'require("qs").parse("foo=bar&baz=1")' as qs;
qs
-------------------------
{"foo":"bar","baz":"1"}
# actually use the parsed query string as json
test=# select qs~>'@foo' as foo from (select ~>'require("qs").parse("foo=bar&baz=1")' as qs) a;
foo
-------
"bar"
# create a user function from qs so we don't have to require it:
% plv8x -f 'plv8x.json parse_qs(text)=qs:parse'
ok plv8x.json parse_qs(text)
# Now parse_qs is a postgresql function:
test=# select parse_qs('foo=bar&baz=1') as qs;
qs
-------------------------
{"foo":"bar","baz":"1"}
We support both synchronous and async functions, as well as bare functions defined in
module.exports
.
By default, the first two arguments to an async (back-call) function is taken
to be error
and result
respectively:
% plv8x -f 'fn(text):text=pkg:' # out = pkg(x)
% plv8x -f 'fn(text):text=pkg:method' # out = pkg.method(in)
% plv8x -f 'fn(text):text=pkg:<-' # pkg(x, cb(err, out))
% plv8x -f 'fn(text):text=pkg:<-method' # pkg.method(x, cb(err, out))
Using an underscore, one can specify exactly which async callback parameter to expect from the lifted function:
% plv8x -f 'fn(text):text=pkg:<-' # pkg(x, cb(err, out))
% plv8x -f 'fn(text):text=pkg:_<-' # pkg(x, cb(out))
% plv8x -f 'fn(text):text=pkg:,_<-' # pkg(x, cb(_0, out))
% plv8x -f 'fn(text):text=pkg:,,_<-' # pkg(x, cb(_0, _1, out))
MIT