I want to use values for joining table or schema name.

with country_information(region, country, lang) as (
  values ('NA', 'US', 'en'), ('EU', 'FR', 'fr'), ('EU', 'IT', 'it'))
select *
from US_data.result as r
left join US_data.promotion as p on r.id = p.id
union all
select *
from EU_data.result as r
left join EU_data.promotion as p on r.id = p.id

In this situation, I want to use a country_information values for joining a table.

e.g

from {country}_data.result as r @country = country_information.country in ('NA')

Just... imagine like this.. I don't know how to do it so It's just example.

Is there any way for using table name with user values?

CodePudding user response：

You can use WITH Queries (Common Table Expressions) like table and join with CTE data.

with country_information(region, country, lang) as (
    values ('NA', 'US', 'en'), ('EU', 'FR', 'fr'), ('EU', 'IT', 'it'))
select *
from US_data.result as r
         left join US_data.promotion as p on r.id = p.id
         left join country_information as ci on r.country = ci.country
union all
select *
from EU_data.result as r
         left join EU_data.promotion as p on r.id = p.id
         left join country_information as ci on r.country = ci.country

Or change above query like that and build sample query to remove duplicate join:

with country_information(region, country, lang) as (
    values ('NA', 'US', 'en'), ('EU', 'FR', 'fr'), ('EU', 'IT', 'it')
),
     data as (select *
              from US_data.result as r
                       left join US_data.promotion as p on r.id = p.id
              union all
              select *
              from EU_data.result as r
                       left join EU_data.promotion as p on r.id = p.id)
select * from data d left join country_information as ci on d.country = ci.country

CodePudding user response：

Use VALUES as derived table and ALIAS:

select  qq.w, qq.e from (values (1, 2), (3, 4)) as qq(w,e)```


https://www.postgresql.org/docs/10/queries-table-expressions.html