Everytime I deploy a new stored procedure, it bothers me I have to update the middle-layer to allow my front-end code to use the function.

My middle-layer between the front-end code and the database simply translates JSON-RPC requests

to SELECT queries calling stored procedures returning JSON back to the front-end.

This is almost perfect.

But, I still have to update a file where I keep all queries, queries.xml , every time I add a new function.



<query name="Get_Bank_Transaction_Fee"

params="Username, Password, Host, TransactionType, ClearingHouse, BankCode, Amount, Currency"

result="hashref">

SELECT * FROM Get_Bank_Transaction_Fee(?, ?, ?, ?, ?, ?, ?, ?)

</query>



This is a quite nice way to separate the SQL code from the programming code, but since the SQL query consists of

nothing more than a SELECT statement, the name of the stored procedure and the input arguments,

it might as well be automatically generated based on the JSON-RPC request.

I wanted to do CREATE FUNCTION and nothing more before the front-end could start using the new function!

PostgreSQL 9.0 introduced calling functions using named parameters, instead of relying on the position of the arguments.



-- this,

SELECT myfunc(foo := 'Hello', bar := 'World!');

-- is the same thing as,

SELECT myfunc(bar := 'World!', foo := 'Hello');

-- before 9.0 you had to do,

SELECT myfunc('Hello','World!');

-- which means you must not get the positions wrong



This nice feature made it trivial and safe to map a JSON-RPC request to a stored procedure.

The method is the named of the stored procedure and the params is simply a key/value hash,

where each key maps to a named input argument.

The only tricky part which required some usage of pg_catalog.pg_proc was to determine if the function

returned a SET OF or a single row.

I’ve created two perl modules to do the trick, DBIx::Pg::CallFunction and pg_proc_jsonrpc.psgi .

http://search.cpan.org/~joeljac/DBIx-Pg-CallFunction/scripts/pg_proc_jsonrpc.psgi

http://search.cpan.org/~joeljac/DBIx-Pg-CallFunction/lib/DBIx/Pg/CallFunction.pm

Proof-of-concept demonstration:



joel@ubuntu:~$ psql -c 'CREATE OR REPLACE FUNCTION myfunc(foo text, bar text) RETURNS TEXT AS $ SELECT $1 || $2 $$ LANGUAGE sql SECURITY DEFINER'

CREATE FUNCTION

joel@ubuntu:~$ curl 'http://localhost/postgres/myfunc?foo=Hello&bar=World'

{

"error" : null,

"result" : "HelloWorld"

}

joel@ubuntu:~$ curl 'http://localhost/postgres/now'

{

"error" : null,

"result" : "2012-06-03 10:46:23.972644+07"

}

joel@ubuntu:~$ curl 'http://localhost/postgres/random'

{

"error" : null,

"result" : "0.664589043240994"

}

joel@ubuntu:~$



Extract from PostgreSQL log:



2012-06-03 10:46:14.270 ICT,"www-data","joel",3601,"[local]",4fcade06.e11,2,"SELECT",2012-06-03 10:46:14 ICT,2/52,0,LOG,00000,"execute dbdpg_p26924_1: SELECT * FROM myfunc(bar := $1,foo := $2);","parameters: $1 = 'World', $2 = 'Hello'",,,,,,,,"pg_proc_jsonrpc"

2012-06-03 10:46:23.972 ICT,"www-data","joel",3603,"[local]",4fcade0f.e13,2,"SELECT",2012-06-03 10:46:23 ICT,2/58,0,LOG,00000,"execute dbdpg_p27386_2: SELECT * FROM now();",,,,,,,,,"pg_proc_jsonrpc"

2012-06-03 10:46:27.732 ICT,"www-data","joel",3605,"[local]",4fcade13.e15,2,"SELECT",2012-06-03 10:46:27 ICT,2/64,0,LOG,00000,"execute dbdpg_p17663_2: SELECT * FROM random();",,,,,,,,,"pg_proc_jsonrpc"



No more middle-layer hassle!

Well, OK, not yet, this is just a proof-of-concept, a lot of work and testing remains until it can be put into production.

I think this could potentially be a very interesting way to make a PostgreSQL database more accessible to the outside world,

in a transparent and convenient way, not requiring any extra work or restarts.

Imagine front-end developers writing their own stored procedures in PLV8 (Javascript) and accessing them using JQuery (Javascript).

All that’s left is the browser and the database!

Less is more.