Syntax error for UPDATE ... RETURNING INTO STRICT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Syntax error for UPDATE ... RETURNING INTO STRICT

Alexander Farber
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


Reply | Threaded
Open this post in threaded view
|

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

pafiti

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 <[hidden email]>
Sent: Tuesday, December 3, 2019 11:12 AM
To: pgsql-general <[hidden email]>
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

 

Reply | Threaded
Open this post in threaded view
|

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

Alexander Farber
Thank you Patrick -

On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE <[hidden email]> 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 

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

Regards
Alex


image001.png (10K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Alexander Farber
Alexander Farber <[hidden email]> writes:
> why does not PostgreSQL 10.11 please like the -

I think you are confusing plpgsql syntax with sql syntax.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by Alexander Farber
On 12/3/19 3:37 AM, Alexander Farber wrote:

> Thank you Patrick -
>
> On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE <[hidden email]
> <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

Alexander Farber
Thanks for your replies!

Tom has hinted that STRICT is pl/pgSQL syntax and not SQL

Regards
Alex
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
On 12/3/19 8:24 AM, Alexander Farber wrote:
> Thanks for your replies!
>
> Tom has hinted that STRICT is pl/pgSQL syntax and not SQL

I finally read the full function and see you declared the LANGUAGE as
sql. Now things make sense:)


> Regards
> Alex
>


--
Adrian Klaver
[hidden email]