CODEWITHSUNDEEP
sqldynamic-programmingdynamic-sqlpostgresql-11
JP Damstra
JP Damstra

Reputation: 613

Dynamically Generate SQL for Logical Sentence

Super tricky question. I have a client wanting to output true/false based on a string sentence that represents a rule.

E.g. if a rule is the string: "56 AND 78", then this means "Return true if Table A has rows with ID 56 and ID 78. Note that it doesn't mean the same row has ID 56 and 78 as that's not possible.

Now, I could go ahead and dynamically generate some pseudo SQL like below for the simple cases:

RETURN QUERY (SELECT EXISTS(SELECT 1
                            FROM table_a
                            WHERE id = 56)
              AND
              SELECT EXISTS(SELECT 1
                            FROM table_a
                            WHERE id = 78))

where the WHERE clauses are generated directly from the rule. This satisfies the simple sentence.

However, the rules can get far more complex.
E.g. "(43 OR 44 OR 47) AND (2182 OR 2179 OR 2183)" which translates in English to "Does Table A have a row that has an (any) ID from one of the first three IDs AND (any) one from the second three IDs?"

I'm struggling to wrap my head around finding a way to dynamically generate SQL as the sentence becomes more complex.

Suggestions would be appreciated!

Upvotes: 0

Views: 212

Answers (2)

Ross Presser
Ross Presser

Reputation: 6255

It sounds as if your sentences follow the following rules:

  1. A number is a small integer.
  2. A term is either a single number, or a set of terms either with AND between each pair, or OR between each pair.
  3. Terms must be enclosed by parentheses in order to be combined with more terms.

If this is the case, then I think you can simply:

  • replace each number with (SELECT EXISTS(SELECT 1 FROM table_a WHERE id = *number*))
  • Leave any parentheses, ANDs and ORs where they are.
  • Enclose the string in one more set of parentheses and prefix RETURN QUERY.

This certainly won't be the simplest SQL that expresses your sentence but I think it will be equivalent.

If you want to optimize more, you can switch (5 or 6 or 7) with (SELECT 1 FROM table_a WHERE id IN (5,6,7)). But that shouldn't be necessary for a correct SQL translation.

Upvotes: 1

Farshid Shekari
Farshid Shekari

Reputation: 2449

You can do in this way

  EXECUTE  IMMEDIATE 'SELECT CASE WHEN EXISTS(SELECT 1
                        FROM table_a
                        WHERE id = :ID1 OR id = :ID2) THEN 1 ELSE 0 END'   
            INTO  order_rec
             USING  56, 78;

at finally return order_rec. for more information you can here.

Upvotes: 0

Related Questions