Quick hack for fetching the contents of a temp table

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

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: