Re: Preventing some SQL commands

On Sun, 2004-11-21 at 16:55 +0100, Thomas Hallgren wrote:
> In a PL language it's sometimes desirable to prevent execution of some
> commands. I would like to prevent the commands "begin [work or
> transaction]", "commit", and "rollback", completely and I would like to
> force the user to use explicit methods for the savepoint methods.

I implemented my own SPI; not for this purpose, but I could [may] use it
for that.
This, of course, would also allow mapping specific utility commands to
my internal methods; instead of inhibiting them (tho, I don't do this
[yet, perhaps]).

> I wonder if there's any way to extract the nature of a command from the
> execution plan returned by SPI_prepare. If not, would it be very
> difficult to add? It doesn't feel optimal to add a home brewed parser
> that parses the statements prior to prepare just to find out if I they
> should prevented.
[snip]
> CmdType SPI_get_command_type(void* executionPlan)

[I see Tom's reply, but considering I already wrote this; here it is
anyways.]

Hrm, I can't help but think it would be better just to allow
fetching/access to the Node tag, (e.g. T_TransactionStmt) as opposed to
creating a new command type.

NodeTag SPI_get_utility_tag(void *execPlan);

Well, something like that. I suppose it would somehow need to handle
compound queries.

Perhaps a filter operation would be a better idea.
Passing a function pointer like:

bool (*SPI_UtilityFilter) (NodeTag aStmt);
To a "void SPI_FilterUtilities(void *execPlan, SPI_UtilityFilter fp)".

Throwing an error if deemed necessary by the pointed to function.

Although, I'm inclined to think that if you require this sort of
flexibility you should probably think about writing your own SPI.

While a ways from complete/stable, my Python "SPI":
http://gborg.postgresql.org/project/postgrespy/cvs/co.php/imp/src/query.c?r=HEAD
http://gborg.postgresql.org/project/postgrespy/cvs/co.php/imp/src/portal.c?r=HEAD

--
Regards,
James William Pye