Thread: returning clause on triggers
| I have a table that has a "on before insert" rule, which looks like this: "On insert to XX do instead insert into YY values(a,b,c) returning a,b,c." It works ok, but the returning clause does not actually return these values when I perform an insert query. So basically if I run a "insert into XX..." query, I get no results back, even though the query executes correctly. This could be because the original query does not have the returning clause, only the trigger has. Long story short, the query should basically do: "Insert into XX(...) values(...) returning [what the trigger retunrs]. Is that possible? Because this would basically replace the "mysql_insert_id" functions that I was using. Thanks in advance. |
Vaduvoiu Tiberiu <vaduvoiutibi@yahoo.com> writes:
> I have a table that has a "on before insert" rule, which looks like this:
> "On insert to XX do instead insert into YY values(a,b,c) returning a,b,c."
> It works ok, but the returning clause does not actually return these values when I perform an insert query. So
basicallyif I run a "insert into XX..." query, I get no results back, even though the query executes correctly. This
couldbe because the original query does not have the returning clause, only the trigger has.
Well, yeah. The RETURNING clause in the rule just instructs the system
how to transform INSERT RETURNING queries that are supposed to be
rewritten by the rule. It does not mean "force INSERTs to act like they
have RETURNING even when they don't". That would be a very surprising
behavior that would break most client code.
regards, tom lane