Re: Quick hack for fetching the contents of a temp table
| От | Souvik Bhattacherjee |
|---|---|
| Тема | Re: Quick hack for fetching the contents of a temp table |
| Дата | |
| Msg-id | CAANrPSfUAhRzhy58V_rHArULq3N3VQC_ZWWRP16NqQRx1FW4ww@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Quick hack for fetching the contents of a temp table (Michel Pelletier <pelletier.michel@gmail.com>) |
| Список | pgsql-general |
This question seems related to the other thread you started on April 18th?
> This is related but perhaps a little complicated than the previous one, since this involves modifying the query that would be executed.
Several of us suggested you use a CTE to materialize your intermediate values. Did you try that?
> I'm working with a temp table here used for storing the intermediate results, which is one of the alternate solutions provided the other day. Not sure how different this would be from using a CTE.
Without going into the motivation behind the application, what I'm interested in here is to modify the query based on a prior query. A simple example follows:
Q1: select * from table1 t1, table2 t2 where t1.cid = t2.cid and attr1 = 5 and attr2 = 10;
Step 1: extract the filter conditions on table1 and table2 from Q1. So we perform the following:
explain select * from table1 t1, table2 t2 where t1.cid = t2.cid and attr1 = 5 and attr2 = 10;
and extract the filter conditions. Let's say, (attr1 = 5) belongs to table1 and (attr2 = 10) belongs to table2.
[We store the extracted filter conditions into a temp table]
Q2. insert into table3(cid, attr1, attr3) (select * from table1 where attr3 = 7);
Step 2: Our intent here is to use the extracted filter condition from table1 and substitute it in Q2. Thus after substitution, Q2 would look like:
insert into table3(cid, attr1, attr3) (select * from table1 where attr1 = 5 and attr3 = 7);
________________________________________________________________________
We want to execute all of it within a transaction, so we have the following multi-statement transaction:
begin;
create temp table mytemp(metastr char(1024)) on commit drop;
insert into mytemp(metastr) (select somefunction1('explain select * from table1 t1, table2 t2 where t1.cid = t2.cid and attr1 = 5 and attr2 = 10'));
rewriteq table1 insert into table3(cid, attr1, attr3) (select * from table1 where attr3 = 7);
commit;
Note this line: rewriteq table1 insert into table3(cid, attr1, attr3) (select * from table1 where attr3 = 7);
We have a flag rewriteq that indicates this query needs to be rewritten and we have an argument table1 that indicates the filter condition in table1 needs to be updated. We also need the contents of mytemp from where we fetch the filter conditions extracted, if any. While this is not exactly a rewrite, more like an update to the query, still we use the term rewrite here. Thus the actual query that will be processed by the postgres is
insert into table3(cid, attr1, attr3) (select * from table1 where attr1 = 5 and attr3 = 7);
Hope this makes the question clearer.
-SB
On Tue, Apr 23, 2019 at 8:38 PM Michel Pelletier <pelletier.michel@gmail.com> wrote:
On Tue, Apr 23, 2019 at 2:56 PM Souvik Bhattacherjee <kivuosb@gmail.com> wrote:Hi,I need to use the return value of the function somefunction1() to modify a query and execute it.While it is easy to fetch the desired result using plpgsql functions(), however modifying and rewriting the query using the plpgsql does not seem straightforward. Therefore I have already written the query modification function within the postgres server since I need to use the results from pg_parse_query() method.This question seems related to the other thread you started on April 18th? Several of us suggested you use a CTE to materialize your intermediate values. Did you try that? The example you provided was not complete, and I asked for a reproducible example of what you were trying to achieve and you didn't provide one. I'm all for encouraging people to hack in the core, but it seems like you have the impression that your goal is not possible in SQL when it very likely is. Changing the core is not only going to be a high maintenance burden on you, but it also implies you have a problem that no one else in 30 years of postgres development has had. What's more likely is that it is possible to do what you want in SQL and we'd like to help you, but we need more information and you need to try the suggestions and answer the questions we ask back of you.I'm happy to be wrong about this, because that means postgres can improve in some way, but first we need more information about what your problem actually is.-Michel
В списке pgsql-general по дате отправления:
Предыдущее
От: Michel PelletierДата:
Сообщение: Re: Quick hack for fetching the contents of a temp table