First, apologies for taking so long to reply to your post.
On Fri, 22 Jun 2012 09:55:13, Robert Haas wrote:
> On Wed, Jun 20, 2012 at 12:24 PM, John Lumby <johnlumby(at)hotmail(dot)com> wrote:
> > An INSERT which has a RETURNING clause and which is to be rewritten based on
> > a rule will be accepted if the rule is an "unconditional DO INSTEAD".
> > In general I believe "unconditional" means "no WHERE clause", but in practice
> > if the rule is of the form
> > CREATE RULE insert_part_history as ON INSERT to history \
> > DO INSTEAD SELECT history_insert_partitioned(NEW) returning NEW.id
> > this is treated as conditional and the query is rejected.
>
> This isn't rejected because the query is treated as condition; it's
> rejected because it's not valid syntax. A SELECT query can't have a
> RETURNING clause, because the target list (i.e. the part that
> immediately follows the SELECT) already serves that purpose. The fact
> that it's in a CREATE RULE statement is irrelevant.
Thanks for correcting me. At the time, it wasn't clear to me whether the RETURNING clause
in a CREATE RULE statement belonged to the CREATE RULE statement or the rule being created,
but now I see it's the latter.
> > . I propose to extend the rule system to recognize cases where the INSERT query specifies
> > RETURNING and the rule promises to return a row, and to then permit this query to run
> > and return the expected row. In effect, to widen the definition of "unconditional"
> > to handle cases such as my testcase.
>
> That already (kind of) works:
>
> [...]
>
> I do notice that the RETURNING clause of the INSERT can't reference
> NEW, which seems like a restriction that we probably ought to lift,
> but it doesn't seem to have much to do with your patch.
>
The main use of my proposal is to be able to return the value of the sequence assigned
to the NEW.id column, so yes that is a serious restriction.
However, even if that restriction is lifted, it will not help with the case where
the rule is an invocation of a function, which is the case I need. For example,
in my testcase, the function is building the SQL statement to be executed including thetable name of the partitioned
childtable, based on the timestamp in the NEW record.
Your comments have helped me realize that my original subject line did not accurately state
my requirement, which should read
"support INSERT INTO...RETURNING with partitioned table using rule which invokes a function"
And for this requirement as re-stated, as far as I can tell, current postgresql has no solution:
. only way to invoke a function in a rewrite rule is with SELECT function()
. SELECT does not permit RETURNING clause
. my proposal provides a way of relaxing this restriction for the case of SELECT in a rewrite rule.
I hope this describes the proposal a bit better.
John