5 ms·

The one thing I've always wanted to be able to do is to be able to control the projection part of the query. So in other words, I'd love to be able tell the sel

The one thing I've always wanted to be able to do is to be able to control the projection part of the query. So in other words, I'd love to be able tell the select to display all attributes that are like '%name%'.

bduerst 10y ago
Have you tried using CASE with HAVING? SELECT ..., CASE WHEN attributes LIKE '%name%' THEN name ELSE null END AS name FROM table HAVING name IS NOT null Or did you mean selecting by the column's name specifically?
- koolba 10y ago
  I think GP means "SELECT all columns that have %foo% in the name of the column". It's not possible directly though you can do it with dynamic SQL by querying the data dictionary and dynamically creating the columns retrieved in the SELECT clause.
  - screature2 10y ago
    I think you can search for column names by querying the information_schema to the table schema (at least in postgres) select table_name from information_schema.columns where column_name ilike '%foo%'; see https://www.postgresql.org/docs/devel/static/infoschema-columns.html https://www.postgresql.org/docs/devel/static/infoschema-colu... For searching for items with '%foo%' in all tables and columns, yeah i think that you're probably right. http://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql http://stackoverflow.com/questions/5350088/how-to-search-a-s...
    - ZenoArrow 10y ago
      > "For searching for items with '%foo%' in all tables and columns" To do this you could perform two queries of the information_schema (one query for '%foo%' in tables, one query for '%foo%' in columns) and join them together with a UNION statement (or if you didn't mind duplicates you could use UNION ALL in this case as it's faster and the results from both queries can have the same fields).
    - JoBrad 10y ago
      You can do this in Hive SQL, too. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-REGEXColumnSpecification https://cwiki.apache.org/confluence/display/Hive/LanguageMan...

bduerst 10y ago

Have you tried using CASE with HAVING? SELECT ..., CASE WHEN attributes LIKE '%name%' THEN name ELSE null END AS name FROM table HAVING name IS NOT null Or did you mean selecting by the column's name specifically?

koolba 10y ago
I think GP means "SELECT all columns that have %foo% in the name of the column". It's not possible directly though you can do it with dynamic SQL by querying the data dictionary and dynamically creating the columns retrieved in the SELECT clause.
- screature2 10y ago
  I think you can search for column names by querying the information_schema to the table schema (at least in postgres) select table_name from information_schema.columns where column_name ilike '%foo%'; see https://www.postgresql.org/docs/devel/static/infoschema-columns.html https://www.postgresql.org/docs/devel/static/infoschema-colu... For searching for items with '%foo%' in all tables and columns, yeah i think that you're probably right. http://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql http://stackoverflow.com/questions/5350088/how-to-search-a-s...
  - ZenoArrow 10y ago
    > "For searching for items with '%foo%' in all tables and columns" To do this you could perform two queries of the information_schema (one query for '%foo%' in tables, one query for '%foo%' in columns) and join them together with a UNION statement (or if you didn't mind duplicates you could use UNION ALL in this case as it's faster and the results from both queries can have the same fields).
  - JoBrad 10y ago
    You can do this in Hive SQL, too. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-REGEXColumnSpecification https://cwiki.apache.org/confluence/display/Hive/LanguageMan...