We have a search method that depending on search params will join 3-5 tables, craft the joins and where section. Only problem is, this is done in rather horrible java code. So using pgtap for tests is not feasible. I want to move the database complexity back to database, almost writing the query construction in the plpgsql or python as stores procedure, any suggestions ?
Unfortunately PostgreSQL won't eliminate unnecessary joins from a view, so I can't just create one view and simple code adding where's, order by, etc.
No, I don't want to use orm.
Thanks.
If returning type of function is always the same - you can achieve that with any pl language in postgres...
before 9.2 we have used plv8 (to return text as formated JSON) - because of we haven't known expected number of columns and type for each column in moment we created function....
From 9.2 you can use any procedural language and return JSON datatype...