diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
commit | 6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch) | |
tree | 657d8194422a5daccecfd42d654b8a245ef7b4c8 /contrib/jsonb_plperl/sql/jsonb_plperlu.sql | |
parent | Initial commit. (diff) | |
download | postgresql-13-upstream.tar.xz postgresql-13-upstream.zip |
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'contrib/jsonb_plperl/sql/jsonb_plperlu.sql')
-rw-r--r-- | contrib/jsonb_plperl/sql/jsonb_plperlu.sql | 121 |
1 files changed, 121 insertions, 0 deletions
diff --git a/contrib/jsonb_plperl/sql/jsonb_plperlu.sql b/contrib/jsonb_plperl/sql/jsonb_plperlu.sql new file mode 100644 index 0000000..c68ef73 --- /dev/null +++ b/contrib/jsonb_plperl/sql/jsonb_plperlu.sql @@ -0,0 +1,121 @@ +CREATE EXTENSION jsonb_plperlu CASCADE; + + +CREATE FUNCTION testHVToJsonb() RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +$val = {a => 1, b => 'boo', c => undef}; +return $val; +$$; + +SELECT testHVToJsonb(); + + +CREATE FUNCTION testAVToJsonb() RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +$val = [{a => 1, b => 'boo', c => undef}, {d => 2}]; +return $val; +$$; + +SELECT testAVToJsonb(); + + +CREATE FUNCTION testSVToJsonb() RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +$val = 1; +return $val; +$$; + +SELECT testSVToJsonb(); + + +CREATE FUNCTION testUVToJsonb() RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +as $$ +$val = ~0; +return $val; +$$; + +-- this might produce either 18446744073709551615 or 4294967295 +SELECT testUVToJsonb() IN ('18446744073709551615'::jsonb, '4294967295'::jsonb); + + +-- this revealed a bug in the original implementation +CREATE FUNCTION testRegexpResultToJsonb() RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +return ('1' =~ m(0\t2)); +$$; + +SELECT testRegexpResultToJsonb(); + + +-- this revealed a different bug +CREATE FUNCTION testTextToJsonbObject(text) RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +my $x = shift; +return {a => $x}; +$$; + +SELECT testTextToJsonbObject('abc'); +SELECT testTextToJsonbObject(NULL); + + +CREATE FUNCTION roundtrip(val jsonb, ref text = '') RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +use Data::Dumper; +$Data::Dumper::Sortkeys = 1; +$Data::Dumper::Indent = 0; +elog(INFO, Dumper($_[0])); +die 'unexpected '.(ref($_[0]) || 'not a').' reference' + if ref($_[0]) ne $_[1]; +return $_[0]; +$$; + + +SELECT roundtrip('null') is null; +SELECT roundtrip('1'); +-- skip because Data::Dumper produces a platform-dependent spelling of infinity +-- SELECT roundtrip('1E+131071'); +SELECT roundtrip('-1'); +SELECT roundtrip('1.2'); +SELECT roundtrip('-1.2'); +SELECT roundtrip('"string"'); +SELECT roundtrip('"NaN"'); + +SELECT roundtrip('true'); +SELECT roundtrip('false'); + +SELECT roundtrip('[]', 'ARRAY'); +SELECT roundtrip('[null, null]', 'ARRAY'); +SELECT roundtrip('[1, 2, 3]', 'ARRAY'); +SELECT roundtrip('[-1, 2, -3]', 'ARRAY'); +SELECT roundtrip('[1.2, 2.3, 3.4]', 'ARRAY'); +SELECT roundtrip('[-1.2, 2.3, -3.4]', 'ARRAY'); +SELECT roundtrip('["string1", "string2"]', 'ARRAY'); +SELECT roundtrip('[["string1", "string2"]]', 'ARRAY'); + +SELECT roundtrip('{}', 'HASH'); +SELECT roundtrip('{"1": null}', 'HASH'); +SELECT roundtrip('{"1": 1}', 'HASH'); +SELECT roundtrip('{"1": -1}', 'HASH'); +SELECT roundtrip('{"1": 1.1}', 'HASH'); +SELECT roundtrip('{"1": -1.1}', 'HASH'); +SELECT roundtrip('{"1": "string1"}', 'HASH'); + +SELECT roundtrip('{"1": {"2": [3, 4, 5]}, "2": 3}', 'HASH'); + + +\set VERBOSITY terse \\ -- suppress cascade details +DROP EXTENSION plperlu CASCADE; |