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
[01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg]<http://www.aqsacom.com/>
From: Alexander Farber <[email protected]>
Sent: Tuesday, December 3, 2019 11:12 AM
To: pgsql-general <[email protected]>
Subject: Syntax error for UPDATE ... RETURNING INTO STRICT
Good morning,
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;
and fails with -
ERROR: 42601: syntax error at or near "INTO"
LINE 11: INTO STRICT out_puzzle;
^
LOCATION: scanner_yyerror, scan.l:1128
Thank you
Alex
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
