Hi st 29. 3. 2023 v 6:54 odesÃlatel Yugo NAGATA <nag...@sraoss.co.jp> napsal:
> Hello, > > > Temporary tables are often used to store transient data in > batch processing and the contents can be accessed multiple > times. However, frequent use of temporary tables has a problem > that the system catalog tends to bloat. I know there has been > several proposals to attack this problem, but I would like to > propose a new one. > > The idea is to use Ephemeral Named Relation (ENR) like a > temporary table. ENR information is not stored into the system > catalog, but in QueryEnvironment, so it never bloat the system > catalog. > > Although we cannot perform insert, update or delete on ENR, > I wonder it could be beneficial if we need to reference to a > result of a query multiple times in a batch processing. > > The attached is a concept patch. This adds a new syntax > "OPEN cursor INTO TABLE tablename" to pl/pgSQL, that stores > a result of the cursor query into a ENR with specified name. > However, this is a tentative interface to demonstrate the > concept of feature. > > Here is an example; > > postgres=# \sf fnc > CREATE OR REPLACE FUNCTION public.fnc() > RETURNS TABLE(sum1 integer, avg1 integer, sum2 integer, avg2 integer) > LANGUAGE plpgsql > AS $function$ > DECLARE > sum1 integer; > sum2 integer; > avg1 integer; > avg2 integer; > curs CURSOR FOR SELECT aid, bid, abalance FROM pgbench_accounts > WHERE abalance BETWEEN 100 AND 200; > BEGIN > OPEN curs INTO TABLE tmp_accounts; > SELECT count(abalance) , avg(abalance) INTO sum1, avg1 > FROM tmp_accounts; > SELECT count(bbalance), avg(bbalance) INTO sum2, avg2 > FROM tmp_accounts a, pgbench_branches b WHERE a.bid = b.bid; > RETURN QUERY SELECT sum1,avg1,sum2,avg2; > END; > $function$ > > postgres=# select fnc(); > fnc > -------------------- > (541,151,541,3937) > (1 row) > > As above, we can use the same query result for multiple > aggregations, and also join it with other tables. > > What do you think of using ENR for this way? > The idea looks pretty good. I think it can be very useful. I am not sure if this design is intuitive. If I remember well, the Oracle's has similar features, and can be nice if we use the same or more similar syntax (although I am not sure how it can be implementable)? I think so PL/SQL design has an advantage, because you don't need to solve the scope of the cursor's assigned table. OPEN curs INTO TABLE tmp_accounts; -- it looks little bit strange. I miss info, so tmp_accounts is not normal table what about OPEN curs INTO CURSOR TABLE xxx; or OPEN curs FOR CURSOR TABLE xxx Regards Pavel > > Regards, > Yugo Nagata > > -- > Yugo NAGATA <nag...@sraoss.co.jp> >
