Thread: Dynamic queries in stored procedure
Hey, We have a search method that depending on search params will join 3-5 tables, craft the joins and where section. Only problemis, 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 asstores procedure, any suggestions ? Unfortunately PostgreSQL won't eliminate unnecessary joins from a view, so I can't just create one view and simple code addingwhere's, order by, etc. No, I don't want to use orm. Thanks.
On 07/05/2013 09:57 AM, Greg Jaskiewicz wrote: > Hey, > > We have a search method that depending on search params will join 3-5 tables, craft the joins and where section. Only problemis, 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 pythonas stores procedure, any suggestions ? > > Unfortunately PostgreSQL won't eliminate unnecessary joins from a view, so I can't just create one view and simple codeadding where's, order by, etc. > > No, I don't want to use orm. > It's a matter of taste. Pretty much every PL has facilities for constructing and running dynamic sql. PLPgsql ,PLPerl, PLV8 ... cheers andrew
2013/7/5 Greg Jaskiewicz <gryzman@gmail.com>
Hey,
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...
Cheers,
Misa
I do this all the time; In fact, I've written a dynamic aggregate engine that uses a sudo bind variable technique & dynamic joins with dependency injection because the table names and query logic are not known at run time - all in plpgsql. sb On 7/5/2013 9:26 AM, Andrew Dunstan wrote: > > On 07/05/2013 09:57 AM, Greg Jaskiewicz wrote: >> Hey, >> >> 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. >> > > It's a matter of taste. Pretty much every PL has facilities for > constructing and running dynamic sql. PLPgsql ,PLPerl, PLV8 ... > > cheers > > andrew > > > >