Thread

1. Regression in Postgres 17?

   Colin 't Hart <colinthart@gmail.com> — 2024-10-22T15:54:29Z

   Hi,
   
   This works in Postgres 15:
   
   pg15> create function json_test(out value text, out json jsonb)
   returns record
   language sql
   as
   $$
     select null::text, null::jsonb;
   $$
   ;
   CREATE FUNCTION
   pg15> select * from json_test();
   ┌───────┬──────┐
   │ value │ json │
   ├───────┼──────┤
   │       │      │
   └───────┴──────┘
   (1 row)
   
   
   In Postgres 17 trying to create the function yields an error:
   
   pg17> create function json_test(out value text, out json jsonb)
   returns record
   language sql
   as
   $$
     select null::text, null::jsonb;
   $$
   ;
   ERROR:  syntax error at or near "jsonb"
   LINE 1: create function json_test(out value text, out json jsonb)
   
   
   Am I doing something wrong? Or is this a regression?
   
   Thanks,
   
   Colin
   

2. Re: Regression in Postgres 17?

   Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2024-10-22T16:02:59Z

   Στις 22/10/24 18:54, ο/η Colin 't Hart έγραψε:
   > Hi,
   >
   > This works in Postgres 15:
   >
   > pg15> create function json_test(out value text, out json jsonb)
   > returns record
   > language sql
   > as
   > $$
   >   select null::text, null::jsonb;
   > $$
   > ;
   > CREATE FUNCTION
   > pg15> select * from json_test();
   > ┌───────┬──────┐
   > │ value │ json │
   > ├───────┼──────┤
   > │       │      │
   > └───────┴──────┘
   > (1 row)
   >
   >
   > In Postgres 17 trying to create the function yields an error:
   >
   > pg17> create function json_test(out value text, out json jsonb)
   > returns record
   > language sql
   > as
   > $$
   >   select null::text, null::jsonb;
   > $$
   > ;
   > ERROR:  syntax error at or near "jsonb"
   > LINE 1: create function json_test(out value text, out json jsonb)
   >
   >
   > Am I doing something wrong? Or is this a regression?
   
   Do this instead :
   
   create function json_test(out value text, out jsonparam jsonb)
   returns record
   language sql
   as
   $$
     select null::text, null::jsonb;
   $$
   ;
   CREATE FUNCTION
   
   apparently json is a reserved word (now) and won't be accepted as 
   function parameter name.
   
   >
   > Thanks,
   >
   > Colin

3. Re: Regression in Postgres 17?

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-10-22T16:03:05Z

   On 10/22/24 08:54, Colin 't Hart wrote:
   > Hi,
   > 
   > This works in Postgres 15:
   > 
   > pg15> create function json_test(out value text, out json jsonb)
   > returns record
   > language sql
   > as
   > $$
   >    select null::text, null::jsonb;
   > $$
   > ;
   > CREATE FUNCTION
   > pg15> select * from json_test();
   > ┌───────┬──────┐
   > │ value │ json │
   > ├───────┼──────┤
   > │       │      │
   > └───────┴──────┘
   > (1 row)
   > 
   > 
   > In Postgres 17 trying to create the function yields an error:
   > 
   > pg17> create function json_test(out value text, out json jsonb)
   > returns record
   > language sql
   > as
   > $$
   >    select null::text, null::jsonb;
   > $$
   > ;
   > ERROR:  syntax error at or near "jsonb"
   > LINE 1: create function json_test(out value text, out json jsonb)
   > 
   > 
   > Am I doing something wrong? Or is this a regression?
   
   Yes you are doing something wrong, naming an argument with a type 
   name(json) is not a good idea. Guessing you got caught by this:
   
   https://www.postgresql.org/docs/16/sql-keywords-appendix.html
   
   JSON 	non-reserved 	reserved 	  	
   
   https://www.postgresql.org/docs/17/sql-keywords-appendix.html
   
   JSON 	non-reserved (cannot be function or type) 	reserved 	  	
   
   
   
   > Thanks,
   > 
   > Colin
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

4. Re: Regression in Postgres 17?

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-22T16:12:46Z

   Adrian Klaver <adrian.klaver@aklaver.com> writes:
   >> In Postgres 17 trying to create the function yields an error:
   >> 
   >> pg17> create function json_test(out value text, out json jsonb)
   >> returns record
   >> ...
   >> ERROR:  syntax error at or near "jsonb"
   >> LINE 1: create function json_test(out value text, out json jsonb)
   >> 
   >> Am I doing something wrong? Or is this a regression?
   
   > Yes you are doing something wrong, naming an argument with a type 
   > name(json) is not a good idea.
   
   The actual problem is that the SQL standards committee invented
   some bizarre syntax that we couldn't parse without making JSON
   a partially-reserved word.  It still works as a type name, but
   in this particular syntax where it's not initially clear which
   names are type names, you lose.  Double-quote the argument name,
   or name it something other than "json".
   
   			regards, tom lane
   
   
   
   

5. Re: Regression in Postgres 17?

   Dominique Devienne <ddevienne@gmail.com> — 2024-10-22T16:16:12Z

   On Tue, Oct 22, 2024 at 6:03 PM Achilleas Mantzios
   <a.mantzios@cloud.gatewaynet.com> wrote:
   > Στις 22/10/24 18:54, ο/η Colin 't Hart έγραψε:
   > This works in Postgres 15:
   > Do this instead :
   > create function json_test(out value text, out jsonparam jsonb)
   ...
   > apparently json is a reserved word (now) and won't be accepted as function parameter name.
   
   Or properly double-quote the param name:
   
   ```
   ddevienne=> show server_version;
    server_version
   ----------------
    17.0
   (1 row)
                                                                  ^
   ddevienne=> create function json_test(out value text, out "json"
   jsonb) returns record language sql as 'select null::text,
   null::jsonb;';
   CREATE FUNCTION
   ddevienne=> select * from json_test();
    value | json
   -------+------
          |
   (1 row)
   ```