Re: Quick hack for fetching the contents of a temp table

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(dot)michel(at)gmail(dot)com>
wrote:

> On Tue, Apr 23, 2019 at 2:56 PM Souvik Bhattacherjee <kivuosb(at)gmail(dot)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
>