wrote:
> CREATE OR REPLACE FUNCTION foo() RETURNS INTEGER AS $$
> DECLARE
> dummy INTEGER;
> BEGIN
> dummy=(SELECT MAX(id) FROM test); -- VALID
> dummy=(UPDATE test SET i=i+10 RETURNING i); -- NOT VALID.. WHY?
> dummy=(INSERT INTO test(i) VALUES (10) RETURNING i); -- NOT VALID..
> WHY?
> RETURN dummy;
> END;
> $$
> Language 'plpgsql' VOLATILE;
>
> I get syntax error in both commented as not valid
>
> I think that since all queries actually returns a expected value,
> and since the function is not marked as stable, there is no reason
> for me to block that from happen.
It doesn't strike me that an UPDATE or INSERT statement with a
RETURNING clause quite meets the definition of an *expression* which
can be arbitrarily included in other statements. The documentation
says that simple assignment like you are attempting can only assign
from an expression, not any arbitrary statement that returns a result
set. Just a little further down the page it shows how to assign the
results from such statements to variables, using the INTO clause.
http://www.postgresql.org/docs/9.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
This is not a bug, but a suggestion for a new feature, which seems to
to me be of dubious value. If you think it's worth the programming
effort to support this alternative syntax, you might try submitting a
patch to implement what you want or offering cash to someone to
program it for you, but it would be best to discuss it on (some
other) list first, to see if there is general support for having a
second syntax for this.
-Kevin