> I've worked (and work) on old projects with hundreds
> of SQL queries spread throughout the code,
Stored procedures are a great way to abstract this out and force good SQL coding practices btw.
How exactly do stored procedures prevent you from writing "BAD SQL"? They do no such thing.
Stored procedures are a great way of adding complexity to your code, complexity equals bugs and longer development time. There are reasons for stored procedures, but using them simply to remove SQL from your front end is not one of them. And certainly not because of a fear of "BAD SQL".
To be clear, they prevent one specific case of bad sql, namely queries that are written like this:
SELECT a.*, $col_list
FROM $table a
JOIN $second_table b ON $join
WHERE $where
$having;
Then when you go to find out how $where is define you see a lot of statements like:
if ($http_query->{date_from}){
$where .= " AND date_entered > " . $dbh->quote($http_query->{date_from});
}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.
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.
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.
my $current_month = Month->new();
my $end_month = Month->new()->plus_months(4);
while ($current_month <= $end_month) {
$yyyymm = $current_month->strftme("%Y%m");
$last_day = $current_month->last_date()->strftime("%Y%m%d");
$first_day = $current_month->first_date()->strftime("%Y%m%d");
$dbh->do("create table my_partitioned_table_$yyyymm
() inherits from my_partitioned_table");
$dbh->do("alter table my_partitioned_table_$yyyymm
add constraint ck_my_partitioned_table_$yyyymm
check (date between '$first_day'::Date and '$last_day'::Date)");
$current_month->next_month();
}