2

I have a Postgresql function with dynamic query. In that query i have to pass a list into the IN() or ANY() as argument. It works fine when it have value in the passing list. But when the list is empty it is throwing an error.

Query:

query := 'SELECT * FROM TABLE_NAME WHERE uri IN ('|| UIRIS ||')';

error: 

ERROR: syntax error at or near ")" 
LINE 4: WHERE uri IN()

Is there a way i can handle empty values in IN() or ANY()?

Akbar Badhusha
  • 2,415
  • 18
  • 30
  • 1
    I don't see how your query will work ever, unless `UIRIS` contains single quotes around each value. Why not use a second query for the case where `UIRIS` is empty? – Tim Biegeleisen Apr 09 '19 at 05:26
  • 2
    `something IN ()` is not supported in PostgreSQL. You can replace it with `something = ANY (ARRAY[])`. Since version 9.2 queries of `a IN (3,4,5)` is internally transformed to `a = ANY (ARRAY[3,4,5])`, and `b NOT IN ('a','b','c')` is transformed into `b <> ALL (ARRAY['a','b','c'])`. Also arrays have a string syntax that you can use if your API does not support arrays. – coladict Apr 09 '19 at 05:33
  • @TimBiegeleisen, we have converted a list into string where each uri start and end with single quote. – Akbar Badhusha Apr 09 '19 at 05:41
  • 1
    I hope you took care not to be vulnerable to SQL injection. – Laurenz Albe Apr 09 '19 at 05:51
  • 1
    If you have a function that is _generating_ a query then the function should also handle empty arguments and omit parts of the generated query or generate defaults. – Ancoron Apr 09 '19 at 07:07
  • Thanks for all these inputs. When i look into the logic a little bit more, i was able to change it without using dynamic query. That and that solved the problem – Akbar Badhusha Apr 09 '19 at 07:17

0 Answers0