Hello,
I'm trying to pass some values between queries in a multi-statement transaction. For example, consider the following representative multi-statement transaction:
begin;
select * from table1 t1, table2 t2 where t1.cid = t2.cid;
delete from table1 where cid in (values-to-be-populated-from-the-previous-query);
commit;
Now, assume that I'm able to get the cid values from table1 that satisfies the equi-join condition and I want to pass those values in the IN condition in the subsequent delete query. Is there a straightforward way to achieve this by modifying the postgresql source code?
I tried doing this by creating a hash table (by following this example:
https://wiki.postgresql.org/wiki/HashTable). The hash key in this case is the current transactionid (which I presume should remain unchanged for the entire duration of the transaction) and the hash value is dynamically allocated. Within the query I can perform a lookup and insert without any problem. However, when I try to do a lookup of the hash value from a different query that did not insert the value originally, I do not get any value. The hash table is able to tell me that the key exists (due to the fact that the key is not dynamically allocated) but doesn't return any value. My guess is that after each query in the multi-statement txn block, the storage for that query is deallocated, which results in the following behaviour.
The hash table approach (although it didn't work) above, IMO, has the drawback that it needs to be locked since there can be other txns that can try to access the hash table as well.
The other approach here would be the serialize the values into a file and then read those values later from the subsequent query. However this is not efficient.
Thus, I'm looking for a method of passing values between queries in a multi-statement block that avoids the disk and does not need locking. I was wondering if there is a way to define a hash table (or any data structure) which is visible only to the current transaction.
-SB