Select the same multiple columns from multiple tables-CodePudding

I am trying to extract specific columns of data from multiple tables which all have the same columns in a single sql query.

As a long winded method I am using the below query:

select application, service, serviceid, item
from table10_01
where service IN ('SERVICE12','SERVICE204') and application = 'My Application'
union
select application, service, serviceid, item
from table10_02
where service IN ('SERVICE12','SERVICE204') and application = 'My Application';

Is there a way I can use the table names available in the information_schema.tables to go through all tables? So where I can just select table wildcard like table10_% in one select query?

I understand i can script this query into a for loop but i was hoping to make the select query directly.

thanks you!

CodePudding user response：

That's not possible with regular SQL. You can either use PL/pgSQL or do it in a loop with the programming language you're using.

You can refer to this link, there's an example of some functions written in PL/pgSQL to select from all tables.