0

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 ?

  • Aha, so they're CTE terms, not true relations. I dunno, and I'm out of time to dig for info, sorry. Maybe have a look at how the deparse code in ruleutils.c handles CTEs - see get_with_clause. – Craig Ringer Oct 6 '17 at 9:09
  • Please post an answer here to your own question if you work it out :) – Craig Ringer Oct 6 '17 at 9:12
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.

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.