Adding "eval" to pl? - Mailing list pgsql-hackers

From Adriaan Joubert
Subject Adding "eval" to pl?
Date
Msg-id 37778A75.1F08C09F@albourne.com
Whole thread Raw
Responses Re: [HACKERS] Adding "eval" to pl?
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: José Soares
Date:
Subject: Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
Next
From: Thomas Lockhart
Date:
Subject: Perl library (was Building Postgres)