I'm trying to make a query that should return records based on a subquery. Here's an example:
select ... where tablename like ('%subquery_result%');
The subquery is generating the string as expected:
appldb=# select '''%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''';
?column?
---------------
'%_p2019_09%'
(1 row)
Using the string above, records are returned:
select tablename from pg_tables
where tablename like '%_p2019_09%' limit 2;
tablename
---------------------
part_p2019_09_26
part_p2019_09_29
(2 rows)
But when I use the complete query I have no return:
appldb=# select tablename
from pg_tables
where tablename like ( select '''%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''' );
tablename
-----------
(0 rows)
I have already tried to remove parentheses around the subquery. But the query got in error.
Did I miss any steps to ensure where clause to interpret the subquery?