Writing Functions - PostgreSQL: Up and Running - page 158

data on both the client side and the database side, using PL/V8 could halve your devel‐
opment efforts, pretty much by cutting and pasting.
You can store a whole set of these validation functions in a modules table. You can then
inject results onto the page but also use the validation functions directly in the database,
as described in Andrew Dunstan's “Loading Useful Modules in PLV8” . This is possible
because the eval function is part of the PL/V8 JavaScript language. The built-in function
allows you to compile functions at startup for later use.
We fed Example 8-17 through an online converter ( js2coffee.org ) , and added a return
statement to generate its CoffeeScript counterpart in Example 8-18 .
Example 8-18. PL/Coffee validate email function
CREATE OR REPLACE FUNCTION
validate_email ( email text ) returns boolean as
$$
re = / \ S +@ \ S + \ . \ S +/
return re . test email
$$
LANGUAGE plcoffee IMMUTABLE STRICT ;
CoffeeScript doesn't look all that different from JavaScript, except for the lack of paren‐
theses, curly braces, and semicolons. The LiveScript version looks exactly like the Cof‐
feeScript except with a LANGUAGE plls specifier.
Writing Aggregate Functions with PL/V8
In Example 8-19 , using PL/V8, we redo the geometric mean aggregate function ( “Writ‐
ing SQL Aggregate Functions” on page 149 ).
Example 8-19. PL/V8 geometric mean aggregate: state transition function
CREATE OR REPLACE FUNCTION geom_mean_state ( prev numeric [ 2 ], next numeric )
RETURNS numeric [ 2 ] AS
$$
return ( next == null || next == 0 ) ? prev :
[( prev [ 0 ] == null ) ? 0 : prev [ 0 ] + Math . log ( next ), prev [ 1 ] + 1 ];
$$
LANGUAGE plv8 IMMUTABLE ;
Example 8-20. PL/V8 geometric mean aggregate: final function
CREATE OR REPLACE FUNCTION geom_mean_final ( in_num numeric [ 2 ])
RETURNS numeric AS
$$
return in_num [ 1 ] > 0 ? Math . exp ( in_num [ 0 ] / in_num [ 1 ]) : 0 ;
$$
LANGUAGE plv8 IMMUTABLE ;