Horton

test_coerce_v35_plus_change.out

application/octet-stream

Download
Filename: test_coerce_v35_plus_change.out
Type: application/octet-stream
Part: 2
Message: Re: remaining sql/json patches 
drop domain if exists char3_domain_not_null CASCADE;
NOTICE:  type "char3_domain_not_null" does not exist, skipping
DROP DOMAIN
drop domain if exists hello CASCADE;
NOTICE:  type "hello" does not exist, skipping
DROP DOMAIN
drop domain if exists int42 CASCADE;
NOTICE:  type "int42" does not exist, skipping
DROP DOMAIN
drop type if exists comp_domain_with_typmod;
NOTICE:  type "comp_domain_with_typmod" does not exist, skipping
DROP TYPE
create domain char3_domain_not_null as char(3) NOT NULL;
CREATE DOMAIN
create domain hello as text NOT NULL check (value = 'hello');
CREATE DOMAIN
create domain int42 as int check (value = 42);
CREATE DOMAIN
CREATE TYPE comp_domain_with_typmod AS (a char3_domain_not_null, b int42);
CREATE TYPE
SELECT JSON_QUERY(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning comp_domain_with_typmod omit quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning comp_domain_with_typmod keep quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning char(3) omit quotes);
ERROR:  JSON_QUERY returning type cannot have type modifier while specify OMIT QUOTES
SELECT JSON_QUERY(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning char(3) keep quotes);
 json_query 
------------
 "(a
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "hello"}', '$.rec' returning hello keep quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "hello"}', '$.rec' returning hello omit quotes);
 json_query 
------------
 hello
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "hello1"}', '$.rec' returning hello keep quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "hello1"}', '$.rec' returning hello omit quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'"hello"','$' RETURNING char3_domain_not_null omit quotes) ;
ERROR:  JSON_QUERY returning type cannot have type modifier while specify OMIT QUOTES
SELECT JSON_QUERY(jsonb'"hello"','$' RETURNING char3_domain_not_null keep quotes) ;
 json_query 
------------
 "he
(1 row)

SELECT JSON_QUERY(jsonb '"[1,2]"', '$' RETURNING char(3) omit quotes);
ERROR:  JSON_QUERY returning type cannot have type modifier while specify OMIT QUOTES
SELECT JSON_QUERY(jsonb '"[1,2]"', '$' RETURNING char(3) keep quotes);
 json_query 
------------
 "[1
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int8);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int2);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int4);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int8 OMIT QUOTES);
 json_query 
------------
        123
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING numeric);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING numeric keep quotes);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING numeric keep quotes null on error);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING numeric keep quotes error on error);
ERROR:  invalid input syntax for type numeric: ""123""
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING numeric omit quotes);
 json_query 
------------
        123
(1 row)

SELECT JSON_QUERY(jsonb '123',   '$' RETURNING numeric);
 json_query 
------------
        123
(1 row)

SELECT JSON_QUERY(jsonb '"hello"','$' RETURNING char(3));
 json_query 
------------
 "he
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING bool);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING real);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING float8);
 json_query 
------------
           
(1 row)

SELECT JSON_QUERY(jsonb '"[1,2]"', '$' RETURNING char(3) omit quotes);
ERROR:  JSON_QUERY returning type cannot have type modifier while specify OMIT QUOTES
SELECT JSON_QUERY(jsonb '"[1,2]"', '$' RETURNING char(3) keep quotes);
 json_query 
------------
 "[1
(1 row)

SELECT JSON_QUERY(jsonb '"[1,2]"', '$' RETURNING varchar(3) keep quotes);
 json_query 
------------
 "[1
(1 row)

SELECT JSON_QUERY(jsonb '"[1,2]"', '$' RETURNING varchar keep quotes);
 json_query 
------------
 "[1,2]"
(1 row)

SELECT JSON_VALUE(jsonb'{"rec": "abcd"}', '$.rec' returning char(3));
 json_value 
------------
 abc
(1 row)

SELECT JSON_VALUE(jsonb'{"rec": "abcd"}', '$.rec' returning char3_domain_not_null);
 json_value 
------------
 abc
(1 row)

SELECT JSON_VALUE(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning comp_domain_with_typmod);
 json_value 
------------
 
(1 row)

SELECT JSON_VALUE(jsonb'{"rec": "(abc,42)"}', '$.rec' returning comp_domain_with_typmod);
 json_value 
------------
 (abc,42)
(1 row)

SELECT JSON_VALUE(jsonb'{"rec": "hello"}', '$.rec' returning hello);
 json_value 
------------
 hello
(1 row)

SELECT JSON_VALUE(jsonb'{"rec": "hello1"}', '$.rec' returning hello);
 json_value 
------------
 
(1 row)

SELECT JSON_VALUE(jsonb'"hello"','$' RETURNING char3_domain_not_null) ;
 json_value 
------------
 hel
(1 row)

SELECT JSON_VALUE(jsonb'"hello"','$' RETURNING char(3));
 json_value 
------------
 hel
(1 row)

SELECT JSON_VALUE(jsonb '"[1,2]"', '$' RETURNING varchar);
 json_value 
------------
 [1,2]
(1 row)

---domain type with not null and null issue
drop domain if exists test;
NOTICE:  type "test" does not exist, skipping
DROP DOMAIN
create domain test as int[] check ( array_length(value,1) =2 and (value[1] = 1 or value[2] = 2));
CREATE DOMAIN
create domain test1 as int[] NOT NULL check (array_length(value,1) =2 and (value[1] = 1 or value[2] = 2));
CREATE DOMAIN
SELECT * from JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning test omit quotes);
 json_query 
------------
 
(1 row)

SELECT * from JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning test keep quotes);
 json_query 
------------
 
(1 row)

SELECT * from JSON_VALUE(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning test);
 json_value 
------------
 
(1 row)

SELECT * from JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning test1 omit quotes);
 json_query 
------------
 
(1 row)

SELECT * from JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning test1 keep quotes);
 json_query 
------------
 
(1 row)

SELECT * from JSON_VALUE(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning test1);
 json_value 
------------
 
(1 row)