Hi,
Is there a quick way to fetch the contents of a temp table from within postgres server code?
The temp table has a single column and there is a single value in the column. Thus, I'm using the temp table as a variable to store a string.
begin;
create temp table mytemp(metastr char(1024)) on commit drop;
The metastr attribute in the temp table is populated by a function as shown below.
insert into mytemp(metastr) (select somefunction1());
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.
I stepped through the "select metastr from mytemp;" query only to find that the process of fetching a tuple and the corresponding attribute is a bit convoluted and I was wondering if something in the lines of this code is possible: https://www.postgresql.org/docs/11/xfunc-c.html#id-1.8.3.13.10
From this code, it appears that the tuple has already been provided in t through the statement:
HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
and before that through the query:
SELECT name, c_overpaid(emp, 1500) AS overpaid
FROM emp
WHERE name = 'Bill' OR name = 'Sam';What I'm looking for here is a method to fetch the tuple from mytemp within the postgres server code and then extract the value from the metastr attribute into a char[].
Since all statements above are within a transaction block, I think we do not need to have read locks while accessing temp table.Thanks,
-SB
