Как преобразовать текст в SQL?

Question

[webviewer]

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)

Есть часть функции, где expression строка и соответственно при запросе на том месте падает ошибка.

Как можно преобразовать это выражение в SQL?

Comments

[luna3956]

Тыц

Answer 1

[Артем Черепахин]

Воспользуйтесь динамическим sql
https://www.postgresql.org/docs/9.1/ecpg-dynamic.html