Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcac7658

Browse files
committed
Add transforms feature
This provides a mechanism for specifying conversions between SQL datatypes and procedural languages. As examples, there are transformsfor hstore and ltree for PL/Perl and PL/Python.reviews by Pavel Stěhule and Andres Freund
1 parentf320cbb commitcac7658

File tree

101 files changed

+6061
-2838
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

101 files changed

+6061
-2838
lines changed

‎contrib/Makefile

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,18 @@ else
7171
ALWAYS_SUBDIRS += sepgsql
7272
endif
7373

74+
ifeq ($(with_perl),yes)
75+
SUBDIRS += hstore_plperl
76+
else
77+
ALWAYS_SUBDIRS += hstore_plperl
78+
endif
79+
80+
ifeq ($(with_python),yes)
81+
SUBDIRS += hstore_plpython ltree_plpython
82+
else
83+
ALWAYS_SUBDIRS += hstore_plpython ltree_plpython
84+
endif
85+
7486
# Missing:
7587
#start-scripts\ (does not have a makefile)
7688

‎contrib/hstore_plperl/.gitignore

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Generated subdirectories
2+
/log/
3+
/results/
4+
/tmp_check/

‎contrib/hstore_plperl/Makefile

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
# contrib/hstore_plperl/Makefile
2+
3+
MODULE_big = hstore_plperl
4+
OBJS = hstore_plperl.o
5+
6+
PG_CPPFLAGS = -I$(top_srcdir)/src/pl/plperl -I$(perl_archlibexp)/CORE -I$(top_srcdir)/contrib/hstore
7+
8+
EXTENSION = hstore_plperl hstore_plperlu
9+
DATA = hstore_plperl--1.0.sql hstore_plperlu--1.0.sql
10+
11+
REGRESS = hstore_plperl create_transform
12+
REGRESS_OPTS = --load-extension=hstore --load-extension=plperl --load-extension=plperlu
13+
EXTRA_INSTALL = contrib/hstore
14+
15+
ifdefUSE_PGXS
16+
PG_CONFIG = pg_config
17+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
18+
include$(PGXS)
19+
else
20+
subdir = contrib/hstore_plperl
21+
top_builddir = ../..
22+
include$(top_builddir)/src/Makefile.global
23+
include$(top_srcdir)/contrib/contrib-global.mk
24+
endif
Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,74 @@
1+
-- general regression test for transforms
2+
DROP EXTENSION IF EXISTS hstore CASCADE;
3+
NOTICE: extension "hstore" does not exist, skipping
4+
DROP EXTENSION IF EXISTS plperl CASCADE;
5+
NOTICE: extension "plperl" does not exist, skipping
6+
DROP EXTENSION IF EXISTS hstore_plperl CASCADE;
7+
NOTICE: extension "hstore_plperl" does not exist, skipping
8+
CREATE EXTENSION hstore;
9+
CREATE EXTENSION plperl;
10+
CREATE FUNCTION hstore_to_plperl(val internal) RETURNS internal
11+
LANGUAGE C STRICT IMMUTABLE
12+
AS '$libdir/hstore_plperl';
13+
CREATE FUNCTION plperl_to_hstore(val internal) RETURNS hstore
14+
LANGUAGE C STRICT IMMUTABLE
15+
AS '$libdir/hstore_plperl';
16+
CREATE TRANSFORM FOR foo LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
17+
ERROR: type "foo" does not exist
18+
CREATE TRANSFORM FOR hstore LANGUAGE foo (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
19+
ERROR: language "foo" does not exist
20+
CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_out(hstore), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
21+
ERROR: return data type of FROM SQL function must be "internal"
22+
CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION internal_in(cstring), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
23+
ERROR: first argument of transform function must be type "internal"
24+
CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- ok
25+
CREATE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal), TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- fail
26+
ERROR: transform for type hstore language plperl already exists
27+
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
28+
CREATE OR REPLACE TRANSFORM FOR hstore LANGUAGE plperl (FROM SQL WITH FUNCTION hstore_to_plperl(internal)); -- ok
29+
CREATE OR REPLACE TRANSFORM FOR hstore LANGUAGE plperl (TO SQL WITH FUNCTION plperl_to_hstore(internal)); -- ok
30+
DROP TRANSFORM IF EXISTS FOR fake_type LANGUAGE plperl;
31+
NOTICE: type "fake_type" does not exist, skipping
32+
DROP TRANSFORM IF EXISTS FOR hstore LANGUAGE fake_lang;
33+
NOTICE: transform for type hstore language fake_lang does not exist, skipping
34+
DROP TRANSFORM FOR foo LANGUAGE plperl;
35+
ERROR: type "foo" does not exist
36+
DROP TRANSFORM FOR hstore LANGUAGE foo;
37+
ERROR: language "foo" does not exist
38+
DROP TRANSFORM FOR hstore LANGUAGE plperl;
39+
DROP TRANSFORM IF EXISTS FOR hstore LANGUAGE plperl;
40+
NOTICE: transform for type hstore language plperl does not exist, skipping
41+
DROP FUNCTION hstore_to_plperl(val internal);
42+
DROP FUNCTION plperl_to_hstore(val internal);
43+
CREATE EXTENSION hstore_plperl;
44+
\dx+ hstore_plperl
45+
Objects in extension "hstore_plperl"
46+
Object Description
47+
--------------------------------------
48+
function hstore_to_plperl(internal)
49+
function plperl_to_hstore(internal)
50+
transform for hstore language plperl
51+
(3 rows)
52+
53+
ALTER EXTENSION hstore_plperl DROP TRANSFORM FOR hstore LANGUAGE plperl;
54+
\dx+ hstore_plperl
55+
Objects in extension "hstore_plperl"
56+
Object Description
57+
-------------------------------------
58+
function hstore_to_plperl(internal)
59+
function plperl_to_hstore(internal)
60+
(2 rows)
61+
62+
ALTER EXTENSION hstore_plperl ADD TRANSFORM FOR hstore LANGUAGE plperl;
63+
\dx+ hstore_plperl
64+
Objects in extension "hstore_plperl"
65+
Object Description
66+
--------------------------------------
67+
function hstore_to_plperl(internal)
68+
function plperl_to_hstore(internal)
69+
transform for hstore language plperl
70+
(3 rows)
71+
72+
DROP EXTENSION hstore CASCADE;
73+
NOTICE: drop cascades to extension hstore_plperl
74+
DROP EXTENSION plperl CASCADE;
Lines changed: 213 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,213 @@
1+
CREATE EXTENSION hstore_plperl;
2+
CREATE EXTENSION hstore_plperlu;
3+
SELECT transforms.udt_schema, transforms.udt_name,
4+
routine_schema, routine_name,
5+
group_name, transform_type
6+
FROM information_schema.transforms JOIN information_schema.routines
7+
USING (specific_catalog, specific_schema, specific_name)
8+
ORDER BY 1, 2, 5, 6;
9+
udt_schema | udt_name | routine_schema | routine_name | group_name | transform_type
10+
------------+----------+----------------+-------------------+------------+----------------
11+
public | hstore | public | hstore_to_plperl | plperl | FROM SQL
12+
public | hstore | public | plperl_to_hstore | plperl | TO SQL
13+
public | hstore | public | hstore_to_plperlu | plperlu | FROM SQL
14+
public | hstore | public | plperlu_to_hstore | plperlu | TO SQL
15+
(4 rows)
16+
17+
-- test hstore -> perl
18+
CREATE FUNCTION test1(val hstore) RETURNS int
19+
LANGUAGE plperlu
20+
TRANSFORM FOR TYPE hstore
21+
AS $$
22+
use Data::Dumper;
23+
$Data::Dumper::Sortkeys = 1;
24+
elog(INFO, Dumper($_[0]));
25+
return scalar(keys %{$_[0]});
26+
$$;
27+
SELECT test1('aa=>bb, cc=>NULL'::hstore);
28+
INFO: $VAR1 = {
29+
'aa' => 'bb',
30+
'cc' => undef
31+
};
32+
33+
CONTEXT: PL/Perl function "test1"
34+
test1
35+
-------
36+
2
37+
(1 row)
38+
39+
CREATE FUNCTION test1none(val hstore) RETURNS int
40+
LANGUAGE plperlu
41+
AS $$
42+
use Data::Dumper;
43+
$Data::Dumper::Sortkeys = 1;
44+
elog(INFO, Dumper($_[0]));
45+
return scalar(keys %{$_[0]});
46+
$$;
47+
SELECT test1none('aa=>bb, cc=>NULL'::hstore);
48+
INFO: $VAR1 = '"aa"=>"bb", "cc"=>NULL';
49+
50+
CONTEXT: PL/Perl function "test1none"
51+
test1none
52+
-----------
53+
0
54+
(1 row)
55+
56+
CREATE FUNCTION test1list(val hstore) RETURNS int
57+
LANGUAGE plperlu
58+
TRANSFORM FOR TYPE hstore
59+
AS $$
60+
use Data::Dumper;
61+
$Data::Dumper::Sortkeys = 1;
62+
elog(INFO, Dumper($_[0]));
63+
return scalar(keys %{$_[0]});
64+
$$;
65+
SELECT test1list('aa=>bb, cc=>NULL'::hstore);
66+
INFO: $VAR1 = {
67+
'aa' => 'bb',
68+
'cc' => undef
69+
};
70+
71+
CONTEXT: PL/Perl function "test1list"
72+
test1list
73+
-----------
74+
2
75+
(1 row)
76+
77+
-- test hstore[] -> perl
78+
CREATE FUNCTION test1arr(val hstore[]) RETURNS int
79+
LANGUAGE plperlu
80+
TRANSFORM FOR TYPE hstore
81+
AS $$
82+
use Data::Dumper;
83+
$Data::Dumper::Sortkeys = 1;
84+
elog(INFO, Dumper($_[0]->[0], $_[0]->[1]));
85+
return scalar(keys %{$_[0]});
86+
$$;
87+
SELECT test1arr(array['aa=>bb, cc=>NULL'::hstore, 'dd=>ee']);
88+
INFO: $VAR1 = {
89+
'aa' => 'bb',
90+
'cc' => undef
91+
};
92+
$VAR2 = {
93+
'dd' => 'ee'
94+
};
95+
96+
CONTEXT: PL/Perl function "test1arr"
97+
test1arr
98+
----------
99+
2
100+
(1 row)
101+
102+
-- test perl -> hstore
103+
CREATE FUNCTION test2() RETURNS hstore
104+
LANGUAGE plperl
105+
TRANSFORM FOR TYPE hstore
106+
AS $$
107+
$val = {a => 1, b => 'boo', c => undef};
108+
return $val;
109+
$$;
110+
SELECT test2();
111+
test2
112+
---------------------------------
113+
"a"=>"1", "b"=>"boo", "c"=>NULL
114+
(1 row)
115+
116+
-- test perl -> hstore[]
117+
CREATE FUNCTION test2arr() RETURNS hstore[]
118+
LANGUAGE plperl
119+
TRANSFORM FOR TYPE hstore
120+
AS $$
121+
$val = [{a => 1, b => 'boo', c => undef}, {d => 2}];
122+
return $val;
123+
$$;
124+
SELECT test2arr();
125+
test2arr
126+
--------------------------------------------------------------
127+
{"\"a\"=>\"1\", \"b\"=>\"boo\", \"c\"=>NULL","\"d\"=>\"2\""}
128+
(1 row)
129+
130+
-- test as part of prepare/execute
131+
CREATE FUNCTION test3() RETURNS void
132+
LANGUAGE plperlu
133+
TRANSFORM FOR TYPE hstore
134+
AS $$
135+
use Data::Dumper;
136+
$Data::Dumper::Sortkeys = 1;
137+
138+
$rv = spi_exec_query(q{SELECT 'aa=>bb, cc=>NULL'::hstore AS col1});
139+
elog(INFO, Dumper($rv->{rows}[0]->{col1}));
140+
141+
$val = {a => 1, b => 'boo', c => undef};
142+
$plan = spi_prepare(q{SELECT $1::text AS col1}, "hstore");
143+
$rv = spi_exec_prepared($plan, {}, $val);
144+
elog(INFO, Dumper($rv->{rows}[0]->{col1}));
145+
$$;
146+
SELECT test3();
147+
INFO: $VAR1 = {
148+
'aa' => 'bb',
149+
'cc' => undef
150+
};
151+
152+
CONTEXT: PL/Perl function "test3"
153+
INFO: $VAR1 = '"a"=>"1", "b"=>"boo", "c"=>NULL';
154+
155+
CONTEXT: PL/Perl function "test3"
156+
test3
157+
-------
158+
159+
(1 row)
160+
161+
-- test trigger
162+
CREATE TABLE test1 (a int, b hstore);
163+
INSERT INTO test1 VALUES (1, 'aa=>bb, cc=>NULL');
164+
SELECT * FROM test1;
165+
a | b
166+
---+------------------------
167+
1 | "aa"=>"bb", "cc"=>NULL
168+
(1 row)
169+
170+
CREATE FUNCTION test4() RETURNS trigger
171+
LANGUAGE plperlu
172+
TRANSFORM FOR TYPE hstore
173+
AS $$
174+
use Data::Dumper;
175+
$Data::Dumper::Sortkeys = 1;
176+
elog(INFO, Dumper($_TD->{new}));
177+
if ($_TD->{new}{a} == 1) {
178+
$_TD->{new}{b} = {a => 1, b => 'boo', c => undef};
179+
}
180+
181+
return "MODIFY";
182+
$$;
183+
CREATE TRIGGER test4 BEFORE UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE test4();
184+
UPDATE test1 SET a = a;
185+
INFO: $VAR1 = {
186+
'a' => '1',
187+
'b' => {
188+
'aa' => 'bb',
189+
'cc' => undef
190+
}
191+
};
192+
193+
CONTEXT: PL/Perl function "test4"
194+
SELECT * FROM test1;
195+
a | b
196+
---+---------------------------------
197+
1 | "a"=>"1", "b"=>"boo", "c"=>NULL
198+
(1 row)
199+
200+
DROP TABLE test1;
201+
DROP FUNCTION test1(hstore);
202+
DROP FUNCTION test1none(hstore);
203+
DROP FUNCTION test1list(hstore);
204+
DROP FUNCTION test1arr(hstore[]);
205+
DROP FUNCTION test2();
206+
DROP FUNCTION test2arr();
207+
DROP FUNCTION test3();
208+
DROP FUNCTION test4();
209+
DROP EXTENSION hstore_plperl;
210+
DROP EXTENSION hstore_plperlu;
211+
DROP EXTENSION hstore;
212+
DROP EXTENSION plperl;
213+
DROP EXTENSION plperlu;
Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
-- make sure the prerequisite libraries are loaded
2+
DO'' LANGUAGE plperl;
3+
SELECTNULL::hstore;
4+
5+
6+
CREATEFUNCTIONhstore_to_plperl(val internal) RETURNS internal
7+
LANGUAGE C STRICT IMMUTABLE
8+
AS'MODULE_PATHNAME';
9+
10+
CREATEFUNCTIONplperl_to_hstore(val internal) RETURNS hstore
11+
LANGUAGE C STRICT IMMUTABLE
12+
AS'MODULE_PATHNAME';
13+
14+
CREATE TRANSFORM FOR hstore LANGUAGE plperl (
15+
FROM SQL WITH FUNCTION hstore_to_plperl(internal),
16+
TO SQL WITH FUNCTION plperl_to_hstore(internal)
17+
);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp