I need to dynamically compile a SQL query.

SELECT s.* , ARRAY( SELECT stt.tag_id FROM subscriber_to_tag stt WHERE stt.channel_id = s.channel_id stt.subscriber_id = s.subscriber_id ) as test FROM s WHERE 1 in test AND 2 in test AND 3 in test

The request gives an error

  ERROR: syntax error at or near "test" LINE 2: ... s.subscriber_id) as test FROM subscribers s WHERE 1 in test

There may be many of these numbers, 1,2,3, and, in order not to make select all the time, I want to save it to a variable and check the occurrence.

Any idea how to do this smartly?

    1 answer 1

    Collect from numbers 1, 2, 3 ... and what kind of array there is and use the operator <@ contained in for it and the array from the subquery. Manual

    Note: You cannot access the result columns from the WHERE clause, because at the time of filtering the WHERE rows, the result columns do not yet exist. Drive the creation of an array from a subquery still inside the subquery in the following form:

      SELECT * FROM ( SELECT ARRAY( SELECT t.col FROM ( SELECT 1 AS col UNION SELECT 2 UNION SELECT 3 ) t ) AS ccol ) tt WHERE ARRAY[1, 2] <@ (ccol) ; --только строки с 1 И 2