Huge SQL query inside PLV8 function

Question

In my PostgreSQL-9.6-database, for better or worse, there are a lot of PLV8 functions. Some of our DB business logic simply has to be done in JavaScript.

However, inside these PLV8 functions there are a lot of database queries in vanilla SQL. That's absolutely fine, if you consider small one-liners.

However, sometimes my SQL statements could contain 400 or 500 lines of code.

I know of two ways to do that:

Break up the query string in JavaScript into new lines:

CREATE OR REPLACE FUNCTION public.my_plv8_function
("paramOne" integer,"paramTwo" integer)

RETURNS jsonb AS

$BODY$    

/* ... Do some magic, which can     
/* ... only be done in PLV8 and not pure SQL ... */

var queryString = 'WITH "myCTE" AS (  ' +

'SELECT  ' +    
'table1.colum1,  ' +    
'table1.colum1,  ' +    
/* ... 400 Lines of SQL code    
with CTEs, subqueries, CASE WHEN, 
COALESCE and so on and so on ...  */
var myInnerSQL = plv8.execute(queryString,[paramOne,paramTwo]);

return myInnerSQL;

$BODY$

LANGUAGE plv8 IMMUTABLE;

The downside is: You lose SQL syntax highlighting in pgAdmin, storing the function will not immediately find syntax errors (you have to execute first in order to detect them), the feedback about which line might throw errors could be inaccurate. And it is hard to handle.

Create an SQL function which is called from within PLV8 functions like

plv8.execute('SELECT my_inner_function(id,$2) FROM my_table WHERE some_column = $1',[paramOne,paramTwo]);

However, as far as I tested: Calling a function for each element of the main table is slower than performing an actual query on that table.

So, is there another, better way for inserting "native" SQL inside a PLV8 function?

Huge SQL query inside PLV8 function

0 Answers0