Thread: Adding "eval" to pl?

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

>
> 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.

    This  is  entirely true for PL/pgSQL. But it isn't for PL/Tcl
    where  you   have   control   over   which   statements   get
    prepared/saved and which not.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #