I have this query on postgree...

SELECT c.schemaname AS MySchema, a.relname AS MyTable, b.attname AS MyColumn    
FROM pg_class a 
INNER JOIN pg_attribute b ON (a.relfilenode = b.attrelid)
INNER JOIN pg_tables c ON (a.relname = c.tablename)
GROUP BY c.schemaname, a.relname, a.relfilenode, b.attname 
HAVING b.attname ILIKE('%PartNameColumn%');

The Result is:

MySchema, MyTable, MyColumn
Schema0   Table0   PartNameColumn0
Schema0   Table0   PartNameColumn1
Schema0   Table1   PartNameColumn0
Schema0   Table1   PartNameColumn1
Schema1   Table0   PartNameColumn0
Schema1   Table0   PartNameColumn1
Schema1   Table1   PartNameColumn0
Schema1   Table1   PartNameColumn1

Now I want to get some similar, but introducing a value:

SELECT * FROM MySchema.MyTable WHERE MySchema.MyTable.MyColumn = 'MyValue';

I don't know how pass the before select and create a new query according results IN ONLY ONE SENTENCE.

Please tell me how to do it in only one Query?

Thank you.


You can't. Columns can tell information that's useful but none of it will get you to a value without sending a query. So you'll have to have two queries,

  1. Getting the column name,
  2. Selecting rows where a specific column name is set to a value.

That second query requires planning. Some queries on column names don't require visiting the heap -- such as an index-only scan. Some are greatly sped up by visiting an index first, so you don't have to visit the entire heap and the values for the column are not stored on the column anyway, they're stored in a heap in PostgreSQL.

This may not be ideal, but it's what you've got to work with. See also

  • Sorry I'm newbie. Thank you!!! What about of using variables & concatenations? like something dba.stackexchange.com/questions/4169/… – QA_Col Jan 3 '18 at 2:05
  • I recognize that you are expert, but I'm a stubborn person. You can understand that I can't to accept all to first time, even being a newbie. What about WITH word ? – QA_Col Jan 3 '18 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.