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.
params="Username, Password, Host, TransactionType, ClearingHouse, BankCode, Amount, Currency"
SELECT * FROM Get_Bank_Transaction_Fee(?, ?, ?, ?, ?, ?, ?, ?)
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.
SELECT myfunc(foo := 'Hello', bar := 'World!');
-- is the same thing as,
SELECT myfunc(bar := 'World!', foo := 'Hello');
-- before 9.0 you had to do,
-- 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.
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,
joel@ubuntu:~$ psql -c 'CREATE OR REPLACE FUNCTION myfunc(foo text, bar text) RETURNS TEXT AS $ SELECT $1 || $2 $$ LANGUAGE sql SECURITY DEFINER'
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"
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.
All that’s left is the browser and the database!
Less is more.