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

Поиск
Список
Период
Сортировка
От Patrick FICHE
Тема RE: Syntax error for UPDATE ... RETURNING INTO STRICT
Дата
Msg-id DB6PR0501MB2359D8D7331F05021ECB257EEF420@DB6PR0501MB2359.eurprd05.prod.outlook.com
обсуждение исходный текст
Ответ на Syntax error for UPDATE ... RETURNING INTO STRICT  (Alexander Farber <alexander.farber@gmail.com>)
Ответы Re: Syntax error for UPDATE ... RETURNING INTO STRICT  (Alexander Farber <alexander.farber@gmail.com>)
Список pgsql-general

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

 

From: Alexander Farber <alexander.farber@gmail.com>
Sent: Tuesday, December 3, 2019 11:12 AM
To: pgsql-general <pgsql-general@postgresql.org>
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

 

Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Alexander Farber
Дата:
Сообщение: Syntax error for UPDATE ... RETURNING INTO STRICT
Следующее
От: Alexander Farber
Дата:
Сообщение: Re: Syntax error for UPDATE ... RETURNING INTO STRICT