How to convert text to SQL?

Question

PostgreSQL

WITH available AS (
                SELECT
                    rp.id_category,
                    COUNT(*)::INTEGER AS item_count
                FROM replica.reference_price rp
                JOIN zone.prices zp ON rp.id = zp.id_reference_price AND zp.id_zone =  _id_zone
                LEFT JOIN zone.offers_for_asup o ON rp.id = o.id_reference_price AND o.id_storage = in_id_storage
                WHERE  expression ||
                  COALESCE( E'\n    AND CASE WHEN o.id_reference_price IS NOT NULL THEN COALESCE( o.eta, NOW() ) ELSE NULL END <= NOW() + INTERVAL ''' || in_available_in_days || ' day''', '' )
                GROUP BY rp.id_category
            ), unavailable AS (
                SELECT
                    rp.id_category,
                    COUNT(*)::INTEGER AS item_count
                FROM replica.reference_price rp
                LEFT JOIN zone.prices zp ON rp.id = zp.id_reference_price AND zp.id_zone = _id_zone
                LEFT JOIN zone.offers_for_asup o ON rp.id = o.id_reference_price AND o.id_storage = in_id_storage
                WHERE expression ||
                  COALESCE( E'\n    AND CASE WHEN o.id_reference_price IS NOT NULL THEN COALESCE( o.eta, NOW() ) ELSE NULL END <= NOW() + INTERVAL ''' || in_available_in_days || ' day''', '' )
                GROUP BY rp.id_category
            )
            SELECT * FROM available
            UNION SELECT * FROM unavailable WHERE NOT exists(SELECT * FROM available)

There is a part of the function where expression is a string and, accordingly, an error occurs at that place when requesting.
How can this expression be converted to SQL?

Answer 1

Use dynamic sql
https://www.postgresql.org/docs/9.1/ecpg-dynamic.html