Обсуждение: Re: BUG #6686: plpgsql Can't assign a variable with the output of a SQL Sentence which is not a SELECT
Re: BUG #6686: plpgsql Can't assign a variable with the output of a SQL Sentence which is not a SELECT
От
"Kevin Grittner"
Дата:
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