There are very few valid cases for stored procedures to generate queries like that. And at least with PostgreSQL, dynamic SQL can be spotted with automated searches pretty easily. It's pretty easy to simply add all filters that might be of interest:
WHERE (date_entered > in_date_from or in_date_from IS NULL) ....
Then you also gain testability. We can write test cases for the stored procedures because they have discrete entry points and make sure they are acting according to spec.
Why can't you use that type of code in your frontend?
Stored procedures make it much harder to understand the code since you can't just read the query - you have to find the stored procedure and check it there. (And be realistic - no one has perfect documentation.)
And heaven forbid you have to change the query - now you need to make sure no one else is using it, and you end up with tons of versions of similar stored procedures, not all in use, and many that could be combined.
It doesn't seem worth it to me.
I use stored procedures for data reduction - I want to keep the data in the database, and process it before sending it out. (Saves bandwidth vs doing it in the front end, and often it's faster too.)
But that's it. Using stored procedures just to wrap ordinary queries is a terrible (but very common) idea. (I am aware that it saves time on generating a query plan, but usually that's a case of premature optimization.)
And using it to enforce code policies? Way overkill.
ars: I don't know if we have reached max depth or if it is just a waiting period. The major issues are:
1) Testability. you can write test cases against a stored proc. You cannot write test cases against a SQL query embedded in the middle of a function in another programming language.
2) Single point of control. You can have one function that runs the stored proc there and check properly for SQL injection, ensuring that everything is free of SQL injection as far as front-to-backend is concerned (and in Pg, that only leaves SQL injection issues in dynamic SQL which can be searched for and audited separately).
So that's why not to put it there.
As to changing the query.... I only use databases which come with transactional DDL so that's not an issue as long as inputs and outputs don't change. If the inputs and outputs have to change, we can handle that too with transactional DDL and the fact that we make the interfaces discoverable. In this case, we'd
BEGIN;
DROP FUNCTION IF EXISTS foo_bar(...);
CREATE OR REPLACE FUNCTION foo_bar(...)...;
COMMENT ON FUNCTION foo_bar(...) IS $$...$$;
COMMIT;
Because of the way Pg works this shouldn't really cause issues for currently running queries.
As for finding the right function, since these are all called on a declarative interface so you know what the function name is quickly, and so it is a matter of quickly finding it in a directory of SQL files.
As for testability, don't underrate this. You can insert data, run your tests, and roll back. So for example for our 1099-MISC and INT handling, we run more than 40 tests on a pre-written data set that we can run without ever committing to production and which will never conflict with production data (and therefore does not lock other users out). That's approx. 20 tests per sproc there.
WHERE (date_entered > in_date_from or in_date_from IS NULL) ....
Then you also gain testability. We can write test cases for the stored procedures because they have discrete entry points and make sure they are acting according to spec.