summaryrefslogtreecommitdiffstats
path: root/contrib/hstore_plperl/expected
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
commit46651ce6fe013220ed397add242004d764fc0153 (patch)
tree6e5299f990f88e60174a1d3ae6e48eedd2688b2b /contrib/hstore_plperl/expected
parentInitial commit. (diff)
downloadpostgresql-14-upstream.tar.xz
postgresql-14-upstream.zip
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'contrib/hstore_plperl/expected')
-rw-r--r--contrib/hstore_plperl/expected/create_transform.out75
-rw-r--r--contrib/hstore_plperl/expected/hstore_plperl.out67
-rw-r--r--contrib/hstore_plperl/expected/hstore_plperlu.out151
3 files changed, 293 insertions, 0 deletions
diff --git a/contrib/hstore_plperl/expected/create_transform.out b/contrib/hstore_plperl/expected/create_transform.out
new file mode 100644
index 0000000..dc72395
--- /dev/null
+++ b/contrib/hstore_plperl/expected/create_transform.out
@@ -0,0 +1,75 @@
+-- general regression test for transforms
+DROP EXTENSION IF EXISTS hstore CASCADE;
+NOTICE: extension "hstore" does not exist, skipping
+DROP EXTENSION IF EXISTS plperl CASCADE;
+NOTICE: extension "plperl" does not exist, skipping
+DROP EXTENSION IF EXISTS hstore_plperl CASCADE;
+NOTICE: extension "hstore_plperl" does not exist, skipping
+CREATE EXTENSION hstore;
+CREATE EXTENSION plperl;
+CREATE FUNCTION hstore_to_plperl(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS '$libdir/hstore_plperl';
+CREATE FUNCTION plperl_to_hstore(val internal) RETURNS hstore
+LANGUAGE C STRICT IMMUTABLE
+AS '$libdir/hstore_plperl';
+CREATE TRANSFORM FOR foo LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
+ERROR: type "foo" does not exist
+CREATE TRANSFORM FOR hstore LANGUAGE foo (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
+ERROR: language "foo" does not exist
+CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_out(hstore), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
+ERROR: return data type of FROM SQL function must be internal
+CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION internal_in(cstring), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
+ERROR: first argument of transform function must be type internal
+CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- ok
+CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
+ERROR: transform for type hstore language "plperl" already exists
+CREATE OR REPLACE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- ok
+CREATE OR REPLACE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal)); -- ok
+CREATE OR REPLACE TRANSFORM FOR hstore LANGUAGE plperl (TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- ok
+COMMENT ON TRANSFORM FOR hstore LANGUAGE plperl IS 'test';
+DROP TRANSFORM IF EXISTS FOR fake_type LANGUAGE plperl;
+NOTICE: type "fake_type" does not exist, skipping
+DROP TRANSFORM IF EXISTS FOR hstore LANGUAGE fake_lang;
+NOTICE: transform for type hstore language "fake_lang" does not exist, skipping
+DROP TRANSFORM FOR foo LANGUAGE plperl;
+ERROR: type "foo" does not exist
+DROP TRANSFORM FOR hstore LANGUAGE foo;
+ERROR: language "foo" does not exist
+DROP TRANSFORM FOR hstore LANGUAGE plperl;
+DROP TRANSFORM IF EXISTS FOR hstore LANGUAGE plperl;
+NOTICE: transform for type hstore language "plperl" does not exist, skipping
+DROP FUNCTION hstore_to_plperl(val internal);
+DROP FUNCTION plperl_to_hstore(val internal);
+CREATE EXTENSION hstore_plperl;
+\dx+ hstore_plperl
+ Objects in extension "hstore_plperl"
+ Object description
+--------------------------------------
+ function hstore_to_plperl(internal)
+ function plperl_to_hstore(internal)
+ transform for hstore language plperl
+(3 rows)
+
+ALTER EXTENSION hstore_plperl DROP TRANSFORM FOR hstore LANGUAGE plperl;
+\dx+ hstore_plperl
+Objects in extension "hstore_plperl"
+ Object description
+-------------------------------------
+ function hstore_to_plperl(internal)
+ function plperl_to_hstore(internal)
+(2 rows)
+
+ALTER EXTENSION hstore_plperl ADD TRANSFORM FOR hstore LANGUAGE plperl;
+\dx+ hstore_plperl
+ Objects in extension "hstore_plperl"
+ Object description
+--------------------------------------
+ function hstore_to_plperl(internal)
+ function plperl_to_hstore(internal)
+ transform for hstore language plperl
+(3 rows)
+
+DROP EXTENSION hstore CASCADE;
+NOTICE: drop cascades to extension hstore_plperl
+DROP EXTENSION plperl CASCADE;
diff --git a/contrib/hstore_plperl/expected/hstore_plperl.out b/contrib/hstore_plperl/expected/hstore_plperl.out
new file mode 100644
index 0000000..1ab09a9
--- /dev/null
+++ b/contrib/hstore_plperl/expected/hstore_plperl.out
@@ -0,0 +1,67 @@
+CREATE EXTENSION hstore_plperl CASCADE;
+NOTICE: installing required extension "hstore"
+NOTICE: installing required extension "plperl"
+SELECT transforms.udt_schema, transforms.udt_name,
+ routine_schema, routine_name,
+ group_name, transform_type
+FROM information_schema.transforms JOIN information_schema.routines
+ USING (specific_catalog, specific_schema, specific_name)
+ORDER BY 1, 2, 5, 6;
+ udt_schema | udt_name | routine_schema | routine_name | group_name | transform_type
+------------+----------+----------------+------------------+------------+----------------
+ public | hstore | public | hstore_to_plperl | plperl | FROM SQL
+ public | hstore | public | plperl_to_hstore | plperl | TO SQL
+(2 rows)
+
+-- test perl -> hstore
+CREATE FUNCTION test2() RETURNS hstore
+LANGUAGE plperl
+TRANSFORM FOR TYPE hstore
+AS $$
+$val = {a => 1, b => 'boo', c => undef};
+return $val;
+$$;
+SELECT test2();
+ test2
+---------------------------------
+ "a"=>"1", "b"=>"boo", "c"=>NULL
+(1 row)
+
+-- test perl -> hstore[]
+CREATE FUNCTION test2arr() RETURNS hstore[]
+LANGUAGE plperl
+TRANSFORM FOR TYPE hstore
+AS $$
+$val = [{a => 1, b => 'boo', c => undef}, {d => 2}];
+return $val;
+$$;
+SELECT test2arr();
+ test2arr
+--------------------------------------------------------------
+ {"\"a\"=>\"1\", \"b\"=>\"boo\", \"c\"=>NULL","\"d\"=>\"2\""}
+(1 row)
+
+-- check error cases
+CREATE OR REPLACE FUNCTION test2() RETURNS hstore
+LANGUAGE plperl
+TRANSFORM FOR TYPE hstore
+AS $$
+return 42;
+$$;
+SELECT test2();
+ERROR: cannot transform non-hash Perl value to hstore
+CONTEXT: PL/Perl function "test2"
+CREATE OR REPLACE FUNCTION test2() RETURNS hstore
+LANGUAGE plperl
+TRANSFORM FOR TYPE hstore
+AS $$
+return [1, 2];
+$$;
+SELECT test2();
+ERROR: cannot transform non-hash Perl value to hstore
+CONTEXT: PL/Perl function "test2"
+DROP FUNCTION test2();
+DROP FUNCTION test2arr();
+DROP EXTENSION hstore_plperl;
+DROP EXTENSION hstore;
+DROP EXTENSION plperl;
diff --git a/contrib/hstore_plperl/expected/hstore_plperlu.out b/contrib/hstore_plperl/expected/hstore_plperlu.out
new file mode 100644
index 0000000..d719d29
--- /dev/null
+++ b/contrib/hstore_plperl/expected/hstore_plperlu.out
@@ -0,0 +1,151 @@
+CREATE EXTENSION hstore_plperlu CASCADE;
+NOTICE: installing required extension "hstore"
+NOTICE: installing required extension "plperlu"
+SELECT transforms.udt_schema, transforms.udt_name,
+ routine_schema, routine_name,
+ group_name, transform_type
+FROM information_schema.transforms JOIN information_schema.routines
+ USING (specific_catalog, specific_schema, specific_name)
+ORDER BY 1, 2, 5, 6;
+ udt_schema | udt_name | routine_schema | routine_name | group_name | transform_type
+------------+----------+----------------+-------------------+------------+----------------
+ public | hstore | public | hstore_to_plperlu | plperlu | FROM SQL
+ public | hstore | public | plperlu_to_hstore | plperlu | TO SQL
+(2 rows)
+
+-- test hstore -> perl
+CREATE FUNCTION test1(val hstore) RETURNS int
+LANGUAGE plperlu
+TRANSFORM FOR TYPE hstore
+AS $$
+use Data::Dumper;
+$Data::Dumper::Sortkeys = 1;
+$Data::Dumper::Indent = 0;
+elog(INFO, Dumper($_[0]));
+return scalar(keys %{$_[0]});
+$$;
+SELECT test1('aa=>bb, cc=>NULL'::hstore);
+INFO: $VAR1 = {'aa' => 'bb','cc' => undef};
+ test1
+-------
+ 2
+(1 row)
+
+CREATE FUNCTION test1none(val hstore) RETURNS int
+LANGUAGE plperlu
+AS $$
+use Data::Dumper;
+$Data::Dumper::Sortkeys = 1;
+$Data::Dumper::Indent = 0;
+elog(INFO, Dumper($_[0]));
+return scalar(keys %{$_[0]});
+$$;
+SELECT test1none('aa=>bb, cc=>NULL'::hstore);
+INFO: $VAR1 = '"aa"=>"bb", "cc"=>NULL';
+ test1none
+-----------
+ 0
+(1 row)
+
+CREATE FUNCTION test1list(val hstore) RETURNS int
+LANGUAGE plperlu
+TRANSFORM FOR TYPE hstore
+AS $$
+use Data::Dumper;
+$Data::Dumper::Sortkeys = 1;
+$Data::Dumper::Indent = 0;
+elog(INFO, Dumper($_[0]));
+return scalar(keys %{$_[0]});
+$$;
+SELECT test1list('aa=>bb, cc=>NULL'::hstore);
+INFO: $VAR1 = {'aa' => 'bb','cc' => undef};
+ test1list
+-----------
+ 2
+(1 row)
+
+-- test hstore[] -> perl
+CREATE FUNCTION test1arr(val hstore[]) RETURNS int
+LANGUAGE plperlu
+TRANSFORM FOR TYPE hstore
+AS $$
+use Data::Dumper;
+$Data::Dumper::Sortkeys = 1;
+$Data::Dumper::Indent = 0;
+elog(INFO, Dumper($_[0]->[0], $_[0]->[1]));
+return scalar(keys %{$_[0]});
+$$;
+SELECT test1arr(array['aa=>bb, cc=>NULL'::hstore, 'dd=>ee']);
+INFO: $VAR1 = {'aa' => 'bb','cc' => undef};$VAR2 = {'dd' => 'ee'};
+ test1arr
+----------
+ 2
+(1 row)
+
+-- test as part of prepare/execute
+CREATE FUNCTION test3() RETURNS void
+LANGUAGE plperlu
+TRANSFORM FOR TYPE hstore
+AS $$
+use Data::Dumper;
+$Data::Dumper::Sortkeys = 1;
+$Data::Dumper::Indent = 0;
+
+$rv = spi_exec_query(q{SELECT 'aa=>bb, cc=>NULL'::hstore AS col1});
+elog(INFO, Dumper($rv->{rows}[0]->{col1}));
+
+$val = {a => 1, b => 'boo', c => undef};
+$plan = spi_prepare(q{SELECT $1::text AS col1}, "hstore");
+$rv = spi_exec_prepared($plan, {}, $val);
+elog(INFO, Dumper($rv->{rows}[0]->{col1}));
+$$;
+SELECT test3();
+INFO: $VAR1 = {'aa' => 'bb','cc' => undef};
+INFO: $VAR1 = '"a"=>"1", "b"=>"boo", "c"=>NULL';
+ test3
+-------
+
+(1 row)
+
+-- test trigger
+CREATE TABLE test1 (a int, b hstore);
+INSERT INTO test1 VALUES (1, 'aa=>bb, cc=>NULL');
+SELECT * FROM test1;
+ a | b
+---+------------------------
+ 1 | "aa"=>"bb", "cc"=>NULL
+(1 row)
+
+CREATE FUNCTION test4() RETURNS trigger
+LANGUAGE plperlu
+TRANSFORM FOR TYPE hstore
+AS $$
+use Data::Dumper;
+$Data::Dumper::Sortkeys = 1;
+$Data::Dumper::Indent = 0;
+elog(INFO, Dumper($_TD->{new}));
+if ($_TD->{new}{a} == 1) {
+ $_TD->{new}{b} = {a => 1, b => 'boo', c => undef};
+}
+
+return "MODIFY";
+$$;
+CREATE TRIGGER test4 BEFORE UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE test4();
+UPDATE test1 SET a = a;
+INFO: $VAR1 = {'a' => '1','b' => {'aa' => 'bb','cc' => undef}};
+SELECT * FROM test1;
+ a | b
+---+---------------------------------
+ 1 | "a"=>"1", "b"=>"boo", "c"=>NULL
+(1 row)
+
+DROP TABLE test1;
+DROP FUNCTION test1(hstore);
+DROP FUNCTION test1none(hstore);
+DROP FUNCTION test1list(hstore);
+DROP FUNCTION test1arr(hstore[]);
+DROP FUNCTION test3();
+DROP FUNCTION test4();
+DROP EXTENSION hstore_plperlu;
+DROP EXTENSION hstore;
+DROP EXTENSION plperlu;