CODEWITHSUNDEEP
postgresqlplpgsql
Kris Cottingham
Kris Cottingham

Reputation: 175

Is it possible in PL/pgSQL to evaluate a string as an expression, not a statement?

I have two database tables:

# \d table_1
      Table "public.table_1"
   Column   |  Type   | Modifiers
------------+---------+-----------
 id         | integer |
 value      | integer |
 date_one   | date    |
 date_two   | date    |
 date_three | date    |

# \d table_2
     Table "public.table_2"
   Column   |  Type   | Modifiers
------------+---------+-----------
 id         | integer |
 table_1_id | integer |
 selector   | text    |

The values in table_2.selector can be one of one, two, or three, and are used to select one of the date columns in table_1.

My first implementation used a CASE:

SELECT value
FROM table_1
INNER JOIN table_2 ON table_2.table_1_id = table_1.id
WHERE CASE table_2.selector
      WHEN 'one' THEN
        table_1.date_one
      WHEN 'two' THEN
        table_1.date_two
      WHEN 'three' THEN
        table_1.date_three
      ELSE
        table_1.date_one
      END BETWEEN ? AND ?

The values for selector are such that I could identify the column of interest as eval(date_#{table_2.selector}), if PL/pgSQL allows evaluation of strings as expressions.

The closest I've been able to find is EXECUTE string, which evaluates entire statements. Is there a way to evaluate expressions?

Upvotes: 4

Views: 973

Answers (1)

klin
klin

Reputation: 121574

In the plpgsql function you can dynamically create any expression. This does not apply, however, in the case you described. The query must be explicitly defined before it is executed, while the choice of the field occurs while the query is executed.

Your query is the best approach. You may try to use a function, but it will not bring any benefits as the essence of the issue will remain unchanged.

Upvotes: 1

Related Questions