While investigating Merlin's bug report here: http://archives.postgresql.org/pgsql-general/2006-10/msg00447.php I realized that we've completely failed to consider the interactions of $subject. In particular, functions.c still thinks that SELECT is the only type of query that can return rows.
ISTM that ideally, a query with RETURNING ought to act like a SELECT for the purposes of a SQL function --- to wit, that the result rows are discarded if it's not the last query in the function, and are returned as the function result if it is. The difficulty with this is that unlike SELECT, a query with RETURNING might be queueing up AFTER triggers, which we shouldn't fire until the query is fully executed. Merlin's report shows that we've already got a problem in the back branches with mishandling of after-trigger state, because we push an AfterTrigger stack level at start of an SQL function command, and then are willing to return from the function with that stack level still active if it's a set-returning function. I think we can fix this in the back branches by the expedient of not pushing a stack level (ie, not calling AfterTriggerBegin/EndQuery) unless it's a non-SELECT command --- SELECT will never queue triggers, and we never return partway through a non-SELECT command. But this falls down for RETURNING queries. I thought about fixing this by extending the AfterTrigger state structure to let it be a tree rather than just a stack, ie, we could temporarily pop the function AfterTrigger status entry without executing any queued triggers, and then push it back on when re-entering the function. This seems horribly messy however, and I'm not sure we could still promise unsurprising order of trigger execution in complicated cases. I think the most promising answer may be to push RETURNING rows into a TupleStore and then read them out from there, which is pretty much the same approach we adopted for RETURNING queries inside portals. This'd allow the query to be executed completely, and its triggers fired, before we return from the SQL function. Comments? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match