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 ?
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.