0

I'm using sql parameter's and I have a query that I'd like to use for two similar queries. The first uses two where clauses the other uses only one of the clauses is there a symbol I can use for the second query to always equal true?

For example:

SELECT path, markdown
  FROM public."Comment"
  where path = 'relative/path/' AND markdown = 'here I am';

Second example:

SELECT path, markdown
  FROM public."Comment"
  where path = '/other/relative/path/' AND markdown = ALL;

All being a stand in for some symbol in the query that would make it effectively only search based on the path (because the markdown where clause would return true on all columns).

  • Is the markdown column nullable? – Martin Smith Jul 18 '16 at 21:17
  • @MartinSmith no it is not – Jack Fraser Jul 18 '16 at 21:18
  • where markdown = markdown would work if it never contains null values – a_horse_with_no_name Jul 18 '16 at 21:22
  • @a_horse_with_no_name I think they are passing in a parameterised string. – Martin Smith Jul 18 '16 at 21:24
  • Can you change the query pattern? E.g. where path = 'relative/path/' AND markdown Like 'here I am'? Then you could just substitute % as the string literal. Though I don't know what the performance implications of doing that in Postgres are. – Martin Smith Jul 18 '16 at 21:24
2

I do not agree with the approach you are thinking of using, but if you are dead set on using it, then I would probably structure the query like this:

SELECT path, markdown
  FROM public."Comment"
  where path = '/other/relative/path/' AND (TRUE OR markdown = 'here I am');

Essentially adding a 3rd parameter to your query .. one use case would use the 3rd param and set the 2nd param to FALSE, while another would set the 2nd param to TRUE..

  • "I do not agree with the approach". Is this because of effect on the execution plans or some other reason? – Martin Smith Jul 18 '16 at 21:41
  • 2
    Just that reusing SQL, unless it's for the exact same purpose, is bound to end up being messy and create coupling where it should not exist. Having a query that gets "all comments where there is a path and a markdown" is a different use case than a query that gets "all comments where there is a path". – Joishi Bodio Jul 18 '16 at 21:48
  • @JoishiBodio After thinking more about it I think you are right that the queries aren't similar enough to only use one query. Thank you for your answer. – Jack Fraser Jul 19 '16 at 3:02
2

Use this query instead:

SELECT path, markdown
FROM   public."Comment"
WHERE  path = $1
AND    ($2 IS NULL OR markdown = $2);

To ignore the second parameter, pass NULL for it.

Recent related question with more info:

1

What language are you calling the query from? I've used query builders to add additional criteria to a base query. In this case, the query builder would add the check on markdown only if there was a value to compare it too.

The AND markdown = ALL conditional as you describe it is no different that if you had no markdown comparison in the WHERE clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.