Postgresql - Get Oid and relation name for relations created by the query

Question

Consider the following TPC-DS benchmark query 50.

with ss as
(select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
from store_sales,date_dim,customer_address
where ss_sold_date_sk = d_date_sk
and ss_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year),

ws as
(select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
from web_sales,date_dim,customer_address
where ws_sold_date_sk = d_date_sk
and ws_bill_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year)

select /* tt */
    ss1.ca_county
   ,ss1.d_year
   ,ws2.web_sales/ws1.web_sales web_q1_q2_increase
   ,ss2.store_sales/ss1.store_sales store_q1_q2_increase
   ,ws3.web_sales/ws2.web_sales web_q2_q3_increase
   ,ss3.store_sales/ss2.store_sales store_q2_q3_increase
from
    ss ss1
   ,ss ss2
   ,ss ss3
   ,ws ws1
   ,ws ws2
   ,ws ws3
where
   ss1.d_qoy = 1
   and ss1.d_year = 2000
   and ss1.ca_county = ss2.ca_county
   and ss2.d_qoy = 2
   and ss2.d_year = 2000
   and ss2.ca_county = ss3.ca_county
   and ss3.d_qoy = 3
   and ss3.d_year = 2000
   and ss1.ca_county = ws1.ca_county
   and ws1.d_qoy = 1
   and ws1.d_year = 2000
   and ws1.ca_county = ws2.ca_county
   and ws2.d_qoy = 2
   and ws2.d_year = 2000
   and ws1.ca_county = ws3.ca_county
   and ws3.d_qoy = 3
   and ws3.d_year =2000
   and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else null end 
     > case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales else null end
and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else null end
     > case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales else null end
order by web_q2_q3_increase;

While executing this query inside the postgres code, I want to be able to get the relation name from relid or Oid. I am able to do that for relations web_sales, date_dim, customer_address and store_sales, i.e. I have their relids, from which I am able to get Oid and relation names.

But for relations ss and ws, which are created in the query, I have a relid, but I get their Oid as zero and thus relation name as null.

is there some way for ss and ws also I can get their names from the relids? is ther no RangeTblEntry for ss and ws ?

Answer 1

I got the solution.

I have the relid of the CTE. (in the example ss1, ss2 etc.)

From the relid I can get RangeTblEntry for each CTE using

RangeTblEntry *rt1 = ((RangeTblEntry *) list_nth(root->parse->rtable, (relid)-1));

For a CTE RangeTblEntry has a field ctename, which stores the name it's a CTE.

You can use rt1->ctename to get the CTE name.