{"id":477,"date":"2013-09-15T21:43:00","date_gmt":"2013-09-15T20:43:00","guid":{"rendered":"http:\/\/www.muratyaman.co.uk\/wp\/?p=477"},"modified":"2020-04-01T12:34:45","modified_gmt":"2020-04-01T11:34:45","slug":"postgresql-and-node-js-100-per-cent-javascript-adventure-part-2","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2013\/09\/postgresql-and-node-js-100-per-cent-javascript-adventure-part-2\/","title":{"rendered":"Postgresql and Node.js 100% Javascript Adventure – Part 2"},"content":{"rendered":"

This is the second part of PostgreSQL, Node.js and JavaScript post<\/a>.<\/p>\n

We can continue by installing PL\/V8:<\/p>\n

\r\nsudo apt-get install postgresql-9.2-plv8\r\n<\/pre>\n
Then we can create the extension in our database.<\/p>\n
\r\nsudo su postgres\r\npsql mydb\r\n<\/pre>\n
And run SQL command below:<\/p>\n
\r\ncreate extension plv8;\r\n<\/pre>\n
Then, we can create functions using PL\/V8 JavaScript language. A simple example is below:<\/p>\n
\r\nCREATE FUNCTION public.service (\r\n  inputs pg_catalog.json\r\n)\r\nRETURNS pg_catalog.json AS\r\n$body$\r\nvar result = {};\r\nvar cmd = 'SELECT * FROM tbl_test';\r\nresult.outputs = plv8.execute(cmd);\r\nresult.row_count = result.outputs.length;\r\nreturn result;\r\n$body$\r\nLANGUAGE 'plv8'\r\nVOLATILE\r\nCALLED ON NULL INPUT\r\nSECURITY INVOKER;\r\n<\/pre>\n
It gets variable inputs as JSON type. It returns a single variable result as JSON type. The inputs can have the structure for various parameters so that we can filter and return some records from a table or view. The outputs can include status, rows, error messages, etc. The next example is slightly more complicated:<\/p>\n
\r\nCREATE OR REPLACE FUNCTION public.service_search (\r\n  inputs pg_catalog.json\r\n)\r\nRETURNS pg_catalog.json AS\r\n$body$\r\nvar r = {};\r\nr.status = '';\r\nr.messages = [];\r\nr.outputs = [];\r\nr.row_count = 0;\r\nif (!inputs.limit || inputs.limit < 0 || inputs.limit > 100) inputs.limit = 100;\r\nif (!inputs.offset || inputs.offset < 0) inputs.offset = 0;\r\ntry {\r\n  r.messages[0] = {\"field\": \"view\", \"valid\": false};\r\n  r.messages[1] = {\"field\": \"id\", \"valid\": false};\r\n  r.messages[2] = {\"field\": \"name\", \"valid\": false};\r\n  \r\n  if (inputs.view && inputs.view != '') {\r\n    r.messages[0].valid = true;\r\n  } else {\r\n    throw 'Invalid view';\r\n  }\r\n  var cmd = 'SELECT * FROM ' + inputs.view;\r\n  var criteria = '';\r\n  \r\n  if (inputs.id && inputs.id > 0) {\r\n    r.messages[1].valid = true;\r\n    criteria += '(id >= $1)';\r\n  }\r\n  if (inputs.name && inputs.name != '') {\r\n    r.messages[2].valid = true;\r\n    criteria += (criteria != '' ? ' and ': '') + '(name like $2)';\r\n  }\r\n  cmd += ' limit ' + inputs.limit;\r\n  cmd += ' offset ' + inputs.offset;\r\n  r.outputs = plv8.execute(cmd);\r\n  r.row_count = r.outputs.length;\r\n  r.status = 'success';\r\n} catch (ex) {\r\n  r.status = 'error';\r\n  r.error = ex;\r\n}\r\nreturn r;\r\n$body$\r\nLANGUAGE 'plv8'\r\nVOLATILE\r\nCALLED ON NULL INPUT\r\nSECURITY INVOKER\r\nCOST 100;\r\n<\/pre>\n
This is a proof of concept that we can have a set of functions to provide CRUD operations, for instance, and even more advanced requirements. To be honest, for now, I cannot see a straight forward way to have OOP coding using this method.<\/p>\n","protected":false},"excerpt":{"rendered":"
This is the second part of PostgreSQL, Node.js and JavaScript post.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[93,166,122,33],"class_list":["post-477","post","type-post","status-publish","format-standard","hentry","category-technology","tag-javascript","tag-node","tag-postgresql","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/477","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=477"}],"version-history":[{"count":3,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/477\/revisions"}],"predecessor-version":[{"id":943,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/477\/revisions\/943"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}