Horton

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, Joel Jacobson <joel@compiler.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-01-19T05:07:37Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow underscores in integer and numeric constants.

  2. Remove special outfuncs/readfuncs handling of RangeVar.catalogname.

  3. Remove extra space from dumped ALTER DEFAULT PRIVILEGES.

  4. Create FKs properly when attaching table as partition

  5. psql: improve tab-complete's handling of variant SQL names.

=# "
>> CREATE COLLATION
>>
>> =# create variable myvariable text collate mycollation;
>> CREATE VARIABLE
>>
>> =# select classid::regclass, objid, objsubid, refclassid::regclass,
>> refobjid, refobjsubid from pg_depend where classid::regclass::text =
>> 'pg_variable' or refclassid::regclass::text = 'pg_variable';
>>    classid   | objid | objsubid |  refclassid  | refobjid | refobjsubid
>> -------------+-------+----------+--------------+----------+-------------
>>  pg_variable | 16407 |        0 | pg_namespace |     2200 |           0
>> (1 row)
>>
>
> fixed
>
>
>>
>> =# let myvariable = 'AA';
>> LET
>>
>> =# select 'AA' collate "en-x-icu" < myvariable;
>>  ?column?
>> ----------
>>  f
>> (1 row)
>>
>> =# select 'AA' collate "en-x-icu" < myvariable collate mycollation;
>> ERROR:  42P21: collation mismatch between explicit collations "en-x-icu"
>> and "mycollation"
>> LINE 1: select 'AA' collate "en-x-icu" < myvariable collate mycollat...
>>
>
> What do you expect?  I don't understand collating well, but it looks
> correct. Minimally the tables have the same behavior.
>
> create collation mycollation (locale = 'fr-FR', provider = 'icu');
> create table foo(mycol text collate mycollation);
> select 'AA' collate "en-x-icu" < mycol from foo;
> ┌──────────┐
> │ ?column? │
> ╞══════════╡
> │ f        │
> └──────────┘
> (1 row)
>
>
> postgres=# select 'AA' collate "en-x-icu" < mycol collate mycollation from
> foo;
> ERROR:  collation mismatch between explicit collations "en-x-icu" and
> "mycollation"
> LINE 1: select 'AA' collate "en-x-icu" < mycol collate mycollation f...
>                                                ^
>
>
here is second test

postgres=# CREATE COLLATION nd2 (
  provider = 'icu',
  locale = '@colStrength=secondary', -- or 'und-u-ks-level2'
  deterministic = false
);
CREATE COLLATION
postgres=# create variable testv as text col

postgres=# create variable testv as text collate nd2;
CREATE VARIABLE
postgres=# let testv = 'Ahoj';
LET
postgres=# select testv = 'AHOJ';
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# select testv = 'AHOJ' collate "default";
┌──────────┐
│ ?column? │
╞══════════╡
│ f        │
└──────────┘
(1 row)

Regards

Pavel




>
>