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?

Answers


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);

Need Your Help

jQuery slideToggle Android Lag

android jquery mobile

I am trying to make a slideToggle work properly on the Android devices. I mean... it works fine, but the slideToggle lags or chops. Here is my code and maybe you guys can help me with this.

how can set bounce back email phpmailer

php email smtp phpmailer email-bounces

I am making a email marketing system in which I am sending emails in bulk and there are chances to bounce some emails. I need a solution so that all the email addresses which are spelt incorrectly or