[PERFORM] 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 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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Dynamic queries in stored procedure
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Dynamic queries in stored procedure
2013/7/5 Greg Jaskiewicz gryz...@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
Re: [PERFORM] Dynamic queries in stored procedure
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
