Is it bad form to join against a subselect like this, to avoid duplicate bind variables?
Here's a simplified sample query, done the "traditional" way (sample queries tested in PostgreSQL 8.3):
PREPARE a AS SELECT * FROM users WHERE uid=$1 OR parent=$2; EXECUTE a(0,0);
The trouble is, it's cumbersome to pass the same bind variable twice (moreso for more complex queries when it's needed half a dozen times or more).
Of course, SQL allows the same bind variable to used multiple times in the same query:
PREPARE b AS SELECT * FROM users WHERE uid=$1 OR parent=$1; EXECUTE b(0);
But many DB API layers don't support this (or don't easily), thus making it even more cumbersome to use this approach.
I can get around this by adding a JOIN against a sub-select as such:
PREPARE c AS SELECT uids.* FROM users JOIN (SELECT $1::INT AS uid) AS x ON true WHERE uids.uid=x.uid OR uids.parent=x.uid; EXECUTE c(0);
Explain shows that the latter option has, as one would expect, the most complex execution plan, however, at least in this example, it consistently executes faster than a, and barely slower than b (although preparing the query takes longer than either of the other two options).
So my question is:
Is this sort of JOIN/sub-select construct considered good or bad form for more complex queries, as a way to avoid repeating bind variables?
Not a direct answer to your question, but in this particular case, you should be able to rewrite your query like this:
PREPARE b AS SELECT * FROM users WHERE $1 IN (uid, parent); EXECUTE b(0);