The only reliable way to prevent SQL injection is to carefully use only parameterized queries ("prepared statements") and avoid string interpolation in queries. In PHP that means
pg_query_params or appropriate PDO module use.
Even then, a multi-layered defense is certainly nice. I try to prevent obvious SQL injection attacks from appearing in identifiers where there's no reason for them to be permitted. If you try to set your username to something including
'); I'll just reject the request. Sure, I can safely insert that string and if the check wasn't there the attack would still do nothing, but it means I don't have potential attack code in my DB where it could be read out and interpolated into another SQL command elsewhere, perhaps by code that thinks that "this value came from the database so it must be safe".
This might save you if someone wrote a buggy PL/PgSQL function that doesn't properly use
quote_literal. The data passes straight through your app fine, then successfully executes the attack via the buggy PL/PgSQL function. Similar issues can arise from a badly written automation script, reporting tool, or all sorts of things.
So: even with data from your own DB, treat it as if it could be user-modifiable if it was ever user modifiable at any stage in its life-cycle. In fact, treat everything as user-modifiable and untrustworthy unless you have an extremely good reason not to.
So, IMO, much like you'll tend to reject obvious XSS attempts even though you know your code will safely handle them if not rejected, you should reject obvious SQL injection attempts even if your code will survive them fine.
Also, note that
pg_query_params isn't magic SQL injection protection. If you write:
pg_query_params("SELECT blah FROM $tablename WHERE somecol = \$1", array($uservalue))
$tablename can be defined by the user, you're still just as stuffed.