Hi Alexander,
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;
Regards,
Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96
From: Alexander Farber <alexander.farber@xxxxxxxxx>
Sent: Tuesday, December 3, 2019 11:12 AM
To: pgsql-general <pgsql-general@xxxxxxxxxxxxxx>
Subject: Syntax error for UPDATE ... RETURNING INTO STRICT
why does not PostgreSQL 10.11 please like the -
CREATE OR REPLACE FUNCTION words_toggle_puzzle(
in_mid bigint
) RETURNS table (
out_puzzle boolean
) AS
$func$
UPDATE words_moves
SET puzzle = NOT puzzle
WHERE mid = in_mid
RETURNING puzzle
INTO STRICT out_puzzle;
$func$ LANGUAGE sql;
ERROR: 42601: syntax error at or near "INTO"
LINE 11: INTO STRICT out_puzzle;
^
LOCATION: scanner_yyerror, scan.l:1128
P.S: Here the table description, with mid being the PK:
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable | Default
---------+--------------------------+-----------+----------+------------------------------------------
mid | bigint | | not null | nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb | | |
score | integer | | |
letters | text | | |
hand | text | | |
puzzle | boolean | | not null | false
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
