Re: SQL/JSON revisited
On Wed, 3 May 2023 at 20:17, Alvaro Herrera wrote: > > I would suggest to start a new thread with updated patches, and then a new > commitfest entry can be created with those. > Whoever starts that new thread, please link link it here, I am keen to follow it ;) Thanks a lot! Thanks a lot for all your hard work btw, it's highly appreciated! Best, Matthias
Re: SQL/JSON revisited
On Wed, 5 Apr 2023 at 09:53, Alvaro Herrera wrote: > > Okay, I've marked the CF entry as committed then. > This was marked as commited in the 2023-03 commitfest, however there are still patches missing (for example the JSON_TABLE one). However, I can not see an entry in the current 2023-07 Commitfest. I think it would be a good idea for a new entry in the current commitfest, just to not forget about the not-yet-commited features. Thanks! Matthias
Re: SQL/JSON: JSON_TABLE
On Tue, 22 Mar 2022 at 15:31, Andrew Dunstan wrote: > > I'm planning on pushing the functions patch set this week and json-table > next week. > Great! Thank you very much!
Re: SQL/JSON: JSON_TABLE
Hi everyone! I am watching this thread since quite a while and I am waiting eagerly a long time already that this feature finally lands in PostgreSQL. Given that in around 2 weeks PostgreSQL 15 will go into feature freeze (in the last years that usually happened around the 8th of April AFAIK), is there any chance this will be committed? As far as I understand the patches are almost ready. Sorry for the noise, I just wanted to draw attention that there are people out there looking forward to JSON_TABLE ;) Thanks everyone for your fantastic work! Matthias On Sun, 13 Mar 2022 at 22:22, Andrew Dunstan wrote: > > On 2/9/22 08:22, Himanshu Upadhyaya wrote: > > On Wed, Feb 2, 2022 at 12:44 AM Andrew Dunstan > wrote: > >> > >> rebased with some review comments attended to. > > I am in process of reviewing these patches, initially, have started > > with 0002-JSON_TABLE-v55.patch. > > Tested many different scenarios with various JSON messages and these > > all are working as expected. Just one question on the below output. > > > > ‘postgres[1406146]=#’SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS > > (a int PATH '$.a' ERROR ON EMPTY)) jt; > > a > > --- > > > > (1 row) > > > > ‘postgres[1406146]=#’SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS > > (a int PATH '$.a' ERROR ON ERROR)) jt; > > a > > --- > > > > (1 row) > > > > is not "ERROR ON ERROR" is expected to give error? > > > I think I understand what's going on here. In the first example 'ERROR > ON EMPTY' causes an error condition, but as the default action for an > error condition is to return null that's what happens. To get an error > raised you would need to say 'ERROR ON EMPTY ERROR ON ERROR'. I don't > know if that's according to spec. It seems kinda screwy, arguably a POLA > violation, although that would hardly be a first for the SQL Standards > body. But I'm speculating here, I'm not a standards lawyer. > > In the second case it looks like there isn't really an error. There > would be if you used 'strict' in the path expression. > > > This whole area needs more documentation. > > > cheers > > > andrew > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com > > > >
Add read-only param to set_config(...) / SET that effects (at least) customized runtime options
Hi,
in MS SQL Server you are able to make an entry of SESSION_CONTEXT read-only
by passing the @read_only param to the sp_set_session_context function:
"[ @read_only= ] { 0 | 1 } A flag of type bit. If 1, then the value for the
specified key cannot be changed again on this logical connection. If 0
(default), then the value can be changed."
See
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql
I propose the same functionally for PostgreSQL - at least when setting
"customized options" (the ones with a dot in the name:
https://www.postgresql.org/docs/10/static/runtime-config-custom.html)
The set_config(...) method therefore would end up like:
set_config(setting_name, new_value, is_local, read_only)
The equalient SET command could look something like:
SET READONLY my_app.some_var = 'foo';
SET READONLY SESSION my_app.some_var = 'foo';
SET READONLY LOCAL my_app.some_var = 'foo';
Of course read-only would default to false for backwards compatibility.
When setting READONLY for on SESSION config then that config is not allowed
to be changed anymore until the connection gets closed - even when running
RESET ALL or DISCARD ALL.
When setting read-only for a transaction config "the effects of the SET
LOCAL command disappear at function exit" will still be true, like written
in https://www.postgresql.org/docs/10/static/sql-set.html.
What do you think?
Regards,
Matthias
