Hi,
I have a problem where an action in a PL function depends on a table
which is determined at run-time. So the steps are the following:
1. trigger gets passed a row
2. table name is looked up in a reference table, depending on a field in
row
3. rows are deleted from the table
In PL this cannot be done, as the execution plans are built once, so
that the tables are fixed. The only PL solution I've come up with is a
giant IF-THEN-ELSE statements, which is not terribly practical and hard
to change.
I thought of writing this in SPI, but the WHERE part of the statement is
causing me problems, as I cannot pass variable numbers of arguments. (I
did see the variable numbers of arguments for triggers, but did not know
how to use this for normal functions.) These deletes happen in several
routines and the number of rows deleted changes according to the
routine. So I would have to implement a separate SPI function for every
case.
So what I really need is some type of 'eval' in PL that builds the query
plan at runtime, but I have no idea how hard this would be to implement.
I was thinking along the lines of
EVAL ''DELETE FROM % WHERE date > %'',tb_name,tb_date;
I guess this probably opens a whole can of worms -- especially if the
executed statement is a SELECT and you want to do something with the
result.
If anybody has any other suggestions on how to handle this situation, I
would be grateful.
Thanks,
Adriaan