Re: Syntax error for UPDATE ... RETURNING INTO STRICT

On 12/3/19 3:37 AM, Alexander Farber wrote:
> Thank you Patrick -
>
> On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE <Patrick(dot)Fiche(at)aqsacom(dot)com
> <mailto:Patrick(dot)Fiche(at)aqsacom(dot)com>> wrote:
>
>
> It seems that STRICT is the issue.____
>
> But why does your function return a table of boolean in this case ?____
>
> As it only updates one record, it would probably be easier to return
> a boolean only.____
>
> CREATE OR REPLACE FUNCTION words_toggle_puzzle(
>                 in_mid     bigint
>         ) RETURNS boolean
>  AS
> $func$
>         UPDATE words_moves
>         SET puzzle = NOT puzzle
>         WHERE mid = in_mid
>         RETURNING puzzle;
> $func$ LANGUAGE sql;
>
>
> your suggestion works well, thank you.
>
> I wanted to use strict, because the mid is a PK - so there should always
> be an exactly one record that has been updated

Which you will get without STRICT:

https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

"For INSERT/UPDATE/DELETE with RETURNING, PL/pgSQL reports an error for
more than one returned row, even when STRICT is not specified. This is
because there is no option such as ORDER BY with which to determine
which affected row should be returned."

Though I still not sure what was wrong with your initial attempt?:

ERROR: 42601: syntax error at or near "INTO"
LINE 11: INTO STRICT out_puzzle;
^
LOCATION: scanner_yyerror, scan.l:1128

From the error it looks like a hidden space issue or something.

>
> (or otherwise, in very strange cases - the SQL would fail and my
> java-servlet would throw SQLException)
>
> Regards
> Alex
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com