SHARE
TWEET

Untitled

a guest Jun 4th, 2019 122 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Hi experts, I believe this tricky task might be interesting FOR you.
  2. WITH settings AS (
  3. SELECT 'key1' AS KEY, 'include' AS what_to_do UNION ALL
  4. SELECT 'key2' AS KEY, 'include' AS what_to_do UNION ALL
  5. SELECT 'key3' AS KEY, 'exclude' AS what_to_do
  6. ),
  7. DATA AS (
  8. SELECT 'value_with_key1_and_other_stuff' AS string_with_data UNION ALL
  9. SELECT 'value_with_key2_and_other_stuff' AS string_with_data UNION ALL
  10. SELECT 'value_with_key3_and_other_stuff' AS string_with_data UNION ALL
  11. SELECT 'value_with_key4_and_other_stuff' AS string_with_data UNION ALL
  12. SELECT 'value_with_key1_key3_and_other_stuff' AS string_with_data UNION ALL
  13. SELECT 'just_a_test' AS string_with_data
  14. )
  15. SELECT * FROM DATA
  16. I got two TABLES, DATA AND settings. Settings TABLE shows KEYS AND what TO do WITH them, include OR exclude. TABLE DATA has strings that contain different VALUES, SOME OF them include KEYS FROM settings, SOME NOT. IN fact, I'm looking for solution to combine below query in the end:
  17. data as (
  18. select 'value_with_key1_and_other_stuff' as string_with_data union all
  19. select 'value_with_key2_and_other_stuff' as string_with_data union all
  20. select 'value_with_key3_and_other_stuff' as string_with_data union all
  21. select 'value_with_key4_and_other_stuff' as string_with_data union all
  22. select 'value_with_key1_key3_and_other_stuff' as string_with_data union all
  23. select 'just_a_test' as string_with_data
  24. )
  25. select * from data
  26. where (upper(string_with_data) like upper('%key1%') or upper(string_with_data) like upper('%key2%')) and upper(string_with_data) not like upper('%key3%');
  27. But instead of hardcoding keys, I'd LIKE TO USE settings TABLE. How do I achieve that?
  28. Oh, AND my PostgreSQL version :
  29. PostgreSQL 10.8 (Ubuntu 10.8-0ubuntu0.18.04.1) ON x86_64-pc-linux-gnu, compiled BY gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top