Horton

test.sql

application/sql

Download
Filename: test.sql
Type: application/sql
Part: 4
Message: Re: remaining sql/json patches 
DROP TYPE IF EXISTS jbpop CASCADE;
CREATE TYPE jbpop AS (a text, b int, c timestamp);
SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning jbpop omit quotes);
SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning jbpop keep quotes);
SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning jbpop keep quotes error on error);
SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] omit quotes);
SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes);
SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes error on error);

SELECT JSON_QUERY(jsonb'{"rec": [1,2,3]}', '$.rec' returning int[] omit quotes);
SELECT JSON_QUERY(jsonb'{"rec": [1,2,3]}', '$.rec' returning int[] keep quotes);

SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range omit quotes);
SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes);
SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' 	returning int4range keep quotes error on error);
SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range);
SELECT JSON_QUERY(jsonb '"world"', '$' returning text WITH WRAPPER);

SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric format json PATH '$' KEEP QUOTES ON SCALAR STRING));
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric format json PATH '$' OMIT QUOTES ON SCALAR STRING));
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' KEEP QUOTES ));
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' KEEP QUOTES error on error));
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' OMIT QUOTES ));

SELECT * FROM JSON_TABLE(jsonb'{"rec": [1,2,3]}', '$.rec' COLUMNS(v1 int[] 	path '$' keep quotes));
SELECT * FROM JSON_TABLE(jsonb'{"rec": [1,2,3]}', '$.rec' COLUMNS(v1 int[] 	path '$' keep quotes error on error));
SELECT * FROM JSON_TABLE(jsonb'{"rec": [1,2,3]}', '$.rec' COLUMNS(v1 int[] 	path '$' omit quotes));

SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range FORMAT JSON path '$' keep quotes));
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range FORMAT JSON path '$' omit quotes));
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' keep quotes));
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' keep quotes error on error));
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' omit quotes));
SELECT * from JSON_TABLE(jsonb'{"rec": "{1,2,3}"}', '$.rec' COLUMNS(v int[] path '$'  omit quotes));
SELECT * from JSON_TABLE(jsonb'{"rec": "{1,2,3}"}', '$.rec' COLUMNS(v int[] path '$'  keep quotes));
SELECT * from JSON_TABLE(jsonb'{"rec": "{1,2,3}"}', '$.rec' COLUMNS(v int[] path '$'  keep quotes error on error));
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a text EXISTS PATH '$.a' keep quotes));
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a text EXISTS PATH '$.a' omit quotes));
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER));