summaryrefslogtreecommitdiffstats
path: root/contrib/hstore_plpython
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/hstore_plpython')
-rw-r--r--contrib/hstore_plpython/.gitignore4
-rw-r--r--contrib/hstore_plpython/Makefile39
-rw-r--r--contrib/hstore_plpython/expected/hstore_plpython.out161
-rw-r--r--contrib/hstore_plpython/hstore_plpython.c192
-rw-r--r--contrib/hstore_plpython/hstore_plpython3u--1.0.sql19
-rw-r--r--contrib/hstore_plpython/hstore_plpython3u.control6
-rw-r--r--contrib/hstore_plpython/sql/hstore_plpython.sql130
7 files changed, 551 insertions, 0 deletions
diff --git a/contrib/hstore_plpython/.gitignore b/contrib/hstore_plpython/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/contrib/hstore_plpython/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/hstore_plpython/Makefile b/contrib/hstore_plpython/Makefile
new file mode 100644
index 0000000..9d88cda
--- /dev/null
+++ b/contrib/hstore_plpython/Makefile
@@ -0,0 +1,39 @@
+# contrib/hstore_plpython/Makefile
+
+MODULE_big = hstore_plpython$(python_majorversion)
+OBJS = \
+ $(WIN32RES) \
+ hstore_plpython.o
+PGFILEDESC = "hstore_plpython - hstore transform for plpython"
+
+EXTENSION = hstore_plpython3u
+DATA = hstore_plpython3u--1.0.sql
+
+REGRESS = hstore_plpython
+
+PG_CPPFLAGS = $(python_includespec) -DPLPYTHON_LIBNAME='"plpython$(python_majorversion)"'
+
+ifdef USE_PGXS
+PG_CPPFLAGS += -I$(includedir_server)/extension
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+PG_CPPFLAGS += -I$(top_srcdir)/src/pl/plpython -I$(top_srcdir)/contrib
+subdir = contrib/hstore_plpython
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+# We must link libpython explicitly
+ifeq ($(PORTNAME), win32)
+# ... see silliness in plpython Makefile ...
+SHLIB_LINK_INTERNAL += $(sort $(wildcard ../../src/pl/plpython/libpython*.a))
+else
+rpathdir = $(python_libdir)
+SHLIB_LINK += $(python_libspec) $(python_additional_libs)
+endif
+
+REGRESS_OPTS += --load-extension=hstore
+EXTRA_INSTALL += contrib/hstore
diff --git a/contrib/hstore_plpython/expected/hstore_plpython.out b/contrib/hstore_plpython/expected/hstore_plpython.out
new file mode 100644
index 0000000..5fb56a2
--- /dev/null
+++ b/contrib/hstore_plpython/expected/hstore_plpython.out
@@ -0,0 +1,161 @@
+CREATE EXTENSION hstore_plpython3u CASCADE;
+NOTICE: installing required extension "plpython3u"
+-- test hstore -> python
+CREATE FUNCTION test1(val hstore) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+SELECT test1('aa=>bb, cc=>NULL'::hstore);
+INFO: [('aa', 'bb'), ('cc', None)]
+ test1
+-------
+ 2
+(1 row)
+
+-- the same with the versioned language name
+CREATE FUNCTION test1n(val hstore) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+SELECT test1n('aa=>bb, cc=>NULL'::hstore);
+INFO: [('aa', 'bb'), ('cc', None)]
+ test1n
+--------
+ 2
+(1 row)
+
+-- test that a non-mapping result is correctly rejected
+CREATE FUNCTION test1bad() RETURNS hstore
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+return "foo"
+$$;
+SELECT test1bad();
+ERROR: not a Python mapping
+CONTEXT: while creating return value
+PL/Python function "test1bad"
+-- test hstore[] -> python
+CREATE FUNCTION test1arr(val hstore[]) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert(val == [{'aa': 'bb', 'cc': None}, {'dd': 'ee'}])
+return len(val)
+$$;
+SELECT test1arr(array['aa=>bb, cc=>NULL'::hstore, 'dd=>ee']);
+ test1arr
+----------
+ 2
+(1 row)
+
+-- test python -> hstore
+CREATE FUNCTION test2(a int, b text) RETURNS hstore
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+val = {'a': a, 'b': b, 'c': None}
+return val
+$$;
+SELECT test2(1, 'boo');
+ test2
+---------------------------------
+ "a"=>"1", "b"=>"boo", "c"=>NULL
+(1 row)
+
+--- test ruleutils
+\sf test2
+CREATE OR REPLACE FUNCTION public.test2(a integer, b text)
+ RETURNS hstore
+ TRANSFORM FOR TYPE hstore
+ LANGUAGE plpython3u
+AS $function$
+val = {'a': a, 'b': b, 'c': None}
+return val
+$function$
+-- test python -> hstore[]
+CREATE FUNCTION test2arr() RETURNS hstore[]
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+val = [{'a': 1, 'b': 'boo', 'c': None}, {'d': 2}]
+return val
+$$;
+SELECT test2arr();
+ test2arr
+--------------------------------------------------------------
+ {"\"a\"=>\"1\", \"b\"=>\"boo\", \"c\"=>NULL","\"d\"=>\"2\""}
+(1 row)
+
+-- test python -> domain over hstore
+CREATE DOMAIN hstore_foo AS hstore CHECK(VALUE ? 'foo');
+CREATE FUNCTION test2dom(fn text) RETURNS hstore_foo
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+return {'a': 1, fn: 'boo', 'c': None}
+$$;
+SELECT test2dom('foo');
+ test2dom
+-----------------------------------
+ "a"=>"1", "c"=>NULL, "foo"=>"boo"
+(1 row)
+
+SELECT test2dom('bar'); -- fail
+ERROR: value for domain hstore_foo violates check constraint "hstore_foo_check"
+CONTEXT: while creating return value
+PL/Python function "test2dom"
+-- test as part of prepare/execute
+CREATE FUNCTION test3() RETURNS void
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+rv = plpy.execute("SELECT 'aa=>bb, cc=>NULL'::hstore AS col1")
+assert(rv[0]["col1"] == {'aa': 'bb', 'cc': None})
+
+val = {'a': 1, 'b': 'boo', 'c': None}
+plan = plpy.prepare("SELECT $1::text AS col1", ["hstore"])
+rv = plpy.execute(plan, [val])
+assert(rv[0]["col1"] == '"a"=>"1", "b"=>"boo", "c"=>NULL')
+$$;
+SELECT test3();
+ 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 plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert(TD["new"] == {'a': 1, 'b': {'aa': 'bb', 'cc': None}})
+if TD["new"]["a"] == 1:
+ TD["new"]["b"] = {'a': 1, 'b': 'boo', 'c': None}
+
+return "MODIFY"
+$$;
+CREATE TRIGGER test4 BEFORE UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE test4();
+UPDATE test1 SET a = a;
+SELECT * FROM test1;
+ a | b
+---+---------------------------------
+ 1 | "a"=>"1", "b"=>"boo", "c"=>NULL
+(1 row)
+
diff --git a/contrib/hstore_plpython/hstore_plpython.c b/contrib/hstore_plpython/hstore_plpython.c
new file mode 100644
index 0000000..4b3ef02
--- /dev/null
+++ b/contrib/hstore_plpython/hstore_plpython.c
@@ -0,0 +1,192 @@
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "hstore/hstore.h"
+#include "plpy_typeio.h"
+#include "plpython.h"
+
+PG_MODULE_MAGIC;
+
+extern void _PG_init(void);
+
+/* Linkage to functions in plpython module */
+typedef char *(*PLyObject_AsString_t) (PyObject *plrv);
+static PLyObject_AsString_t PLyObject_AsString_p;
+typedef PyObject *(*PLyUnicode_FromStringAndSize_t) (const char *s, Py_ssize_t size);
+static PLyUnicode_FromStringAndSize_t PLyUnicode_FromStringAndSize_p;
+
+/* Linkage to functions in hstore module */
+typedef HStore *(*hstoreUpgrade_t) (Datum orig);
+static hstoreUpgrade_t hstoreUpgrade_p;
+typedef int (*hstoreUniquePairs_t) (Pairs *a, int32 l, int32 *buflen);
+static hstoreUniquePairs_t hstoreUniquePairs_p;
+typedef HStore *(*hstorePairs_t) (Pairs *pairs, int32 pcount, int32 buflen);
+static hstorePairs_t hstorePairs_p;
+typedef size_t (*hstoreCheckKeyLen_t) (size_t len);
+static hstoreCheckKeyLen_t hstoreCheckKeyLen_p;
+typedef size_t (*hstoreCheckValLen_t) (size_t len);
+static hstoreCheckValLen_t hstoreCheckValLen_p;
+
+
+/*
+ * Module initialize function: fetch function pointers for cross-module calls.
+ */
+void
+_PG_init(void)
+{
+ /* Asserts verify that typedefs above match original declarations */
+ AssertVariableIsOfType(&PLyObject_AsString, PLyObject_AsString_t);
+ PLyObject_AsString_p = (PLyObject_AsString_t)
+ load_external_function("$libdir/" PLPYTHON_LIBNAME, "PLyObject_AsString",
+ true, NULL);
+ AssertVariableIsOfType(&PLyUnicode_FromStringAndSize, PLyUnicode_FromStringAndSize_t);
+ PLyUnicode_FromStringAndSize_p = (PLyUnicode_FromStringAndSize_t)
+ load_external_function("$libdir/" PLPYTHON_LIBNAME, "PLyUnicode_FromStringAndSize",
+ true, NULL);
+ AssertVariableIsOfType(&hstoreUpgrade, hstoreUpgrade_t);
+ hstoreUpgrade_p = (hstoreUpgrade_t)
+ load_external_function("$libdir/hstore", "hstoreUpgrade",
+ true, NULL);
+ AssertVariableIsOfType(&hstoreUniquePairs, hstoreUniquePairs_t);
+ hstoreUniquePairs_p = (hstoreUniquePairs_t)
+ load_external_function("$libdir/hstore", "hstoreUniquePairs",
+ true, NULL);
+ AssertVariableIsOfType(&hstorePairs, hstorePairs_t);
+ hstorePairs_p = (hstorePairs_t)
+ load_external_function("$libdir/hstore", "hstorePairs",
+ true, NULL);
+ AssertVariableIsOfType(&hstoreCheckKeyLen, hstoreCheckKeyLen_t);
+ hstoreCheckKeyLen_p = (hstoreCheckKeyLen_t)
+ load_external_function("$libdir/hstore", "hstoreCheckKeyLen",
+ true, NULL);
+ AssertVariableIsOfType(&hstoreCheckValLen, hstoreCheckValLen_t);
+ hstoreCheckValLen_p = (hstoreCheckValLen_t)
+ load_external_function("$libdir/hstore", "hstoreCheckValLen",
+ true, NULL);
+}
+
+
+/* These defines must be after the module init function */
+#define PLyObject_AsString PLyObject_AsString_p
+#define PLyUnicode_FromStringAndSize PLyUnicode_FromStringAndSize_p
+#define hstoreUpgrade hstoreUpgrade_p
+#define hstoreUniquePairs hstoreUniquePairs_p
+#define hstorePairs hstorePairs_p
+#define hstoreCheckKeyLen hstoreCheckKeyLen_p
+#define hstoreCheckValLen hstoreCheckValLen_p
+
+
+PG_FUNCTION_INFO_V1(hstore_to_plpython);
+
+Datum
+hstore_to_plpython(PG_FUNCTION_ARGS)
+{
+ HStore *in = PG_GETARG_HSTORE_P(0);
+ int i;
+ int count = HS_COUNT(in);
+ char *base = STRPTR(in);
+ HEntry *entries = ARRPTR(in);
+ PyObject *dict;
+
+ dict = PyDict_New();
+ if (!dict)
+ ereport(ERROR,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+ errmsg("out of memory")));
+
+ for (i = 0; i < count; i++)
+ {
+ PyObject *key;
+
+ key = PLyUnicode_FromStringAndSize(HSTORE_KEY(entries, base, i),
+ HSTORE_KEYLEN(entries, i));
+ if (HSTORE_VALISNULL(entries, i))
+ PyDict_SetItem(dict, key, Py_None);
+ else
+ {
+ PyObject *value;
+
+ value = PLyUnicode_FromStringAndSize(HSTORE_VAL(entries, base, i),
+ HSTORE_VALLEN(entries, i));
+ PyDict_SetItem(dict, key, value);
+ Py_XDECREF(value);
+ }
+ Py_XDECREF(key);
+ }
+
+ return PointerGetDatum(dict);
+}
+
+
+PG_FUNCTION_INFO_V1(plpython_to_hstore);
+
+Datum
+plpython_to_hstore(PG_FUNCTION_ARGS)
+{
+ PyObject *dict;
+ PyObject *volatile items;
+ Py_ssize_t pcount;
+ HStore *volatile out;
+
+ dict = (PyObject *) PG_GETARG_POINTER(0);
+
+ /*
+ * As of Python 3, PyMapping_Check() is unreliable unless one first checks
+ * that the object isn't a sequence. (Cleaner solutions exist, but not
+ * before Python 3.10, which we're not prepared to require yet.)
+ */
+ if (PySequence_Check(dict) || !PyMapping_Check(dict))
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("not a Python mapping")));
+
+ pcount = PyMapping_Size(dict);
+ items = PyMapping_Items(dict);
+
+ PG_TRY();
+ {
+ int32 buflen;
+ Py_ssize_t i;
+ Pairs *pairs;
+
+ pairs = palloc(pcount * sizeof(*pairs));
+
+ for (i = 0; i < pcount; i++)
+ {
+ PyObject *tuple;
+ PyObject *key;
+ PyObject *value;
+
+ tuple = PyList_GetItem(items, i);
+ key = PyTuple_GetItem(tuple, 0);
+ value = PyTuple_GetItem(tuple, 1);
+
+ pairs[i].key = PLyObject_AsString(key);
+ pairs[i].keylen = hstoreCheckKeyLen(strlen(pairs[i].key));
+ pairs[i].needfree = true;
+
+ if (value == Py_None)
+ {
+ pairs[i].val = NULL;
+ pairs[i].vallen = 0;
+ pairs[i].isnull = true;
+ }
+ else
+ {
+ pairs[i].val = PLyObject_AsString(value);
+ pairs[i].vallen = hstoreCheckValLen(strlen(pairs[i].val));
+ pairs[i].isnull = false;
+ }
+ }
+
+ pcount = hstoreUniquePairs(pairs, pcount, &buflen);
+ out = hstorePairs(pairs, pcount, buflen);
+ }
+ PG_FINALLY();
+ {
+ Py_DECREF(items);
+ }
+ PG_END_TRY();
+
+ PG_RETURN_POINTER(out);
+}
diff --git a/contrib/hstore_plpython/hstore_plpython3u--1.0.sql b/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
new file mode 100644
index 0000000..0b410ab
--- /dev/null
+++ b/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/hstore_plpython/hstore_plpython3u--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION hstore_plpython3u" to load this file. \quit
+
+CREATE FUNCTION hstore_to_plpython3(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'hstore_to_plpython';
+
+CREATE FUNCTION plpython3_to_hstore(val internal) RETURNS hstore
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME', 'plpython_to_hstore';
+
+CREATE TRANSFORM FOR hstore LANGUAGE plpython3u (
+ FROM SQL WITH FUNCTION hstore_to_plpython3(internal),
+ TO SQL WITH FUNCTION plpython3_to_hstore(internal)
+);
+
+COMMENT ON TRANSFORM FOR hstore LANGUAGE plpython3u IS 'transform between hstore and Python dict';
diff --git a/contrib/hstore_plpython/hstore_plpython3u.control b/contrib/hstore_plpython/hstore_plpython3u.control
new file mode 100644
index 0000000..d86f38e
--- /dev/null
+++ b/contrib/hstore_plpython/hstore_plpython3u.control
@@ -0,0 +1,6 @@
+# hstore_plpython3u extension
+comment = 'transform between hstore and plpython3u'
+default_version = '1.0'
+module_pathname = '$libdir/hstore_plpython3'
+relocatable = true
+requires = 'hstore,plpython3u'
diff --git a/contrib/hstore_plpython/sql/hstore_plpython.sql b/contrib/hstore_plpython/sql/hstore_plpython.sql
new file mode 100644
index 0000000..ebd61e6
--- /dev/null
+++ b/contrib/hstore_plpython/sql/hstore_plpython.sql
@@ -0,0 +1,130 @@
+CREATE EXTENSION hstore_plpython3u CASCADE;
+
+
+-- test hstore -> python
+CREATE FUNCTION test1(val hstore) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+
+SELECT test1('aa=>bb, cc=>NULL'::hstore);
+
+
+-- the same with the versioned language name
+CREATE FUNCTION test1n(val hstore) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+
+SELECT test1n('aa=>bb, cc=>NULL'::hstore);
+
+
+-- test that a non-mapping result is correctly rejected
+CREATE FUNCTION test1bad() RETURNS hstore
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+return "foo"
+$$;
+
+SELECT test1bad();
+
+
+-- test hstore[] -> python
+CREATE FUNCTION test1arr(val hstore[]) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert(val == [{'aa': 'bb', 'cc': None}, {'dd': 'ee'}])
+return len(val)
+$$;
+
+SELECT test1arr(array['aa=>bb, cc=>NULL'::hstore, 'dd=>ee']);
+
+
+-- test python -> hstore
+CREATE FUNCTION test2(a int, b text) RETURNS hstore
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+val = {'a': a, 'b': b, 'c': None}
+return val
+$$;
+
+SELECT test2(1, 'boo');
+
+--- test ruleutils
+\sf test2
+
+
+-- test python -> hstore[]
+CREATE FUNCTION test2arr() RETURNS hstore[]
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+val = [{'a': 1, 'b': 'boo', 'c': None}, {'d': 2}]
+return val
+$$;
+
+SELECT test2arr();
+
+
+-- test python -> domain over hstore
+CREATE DOMAIN hstore_foo AS hstore CHECK(VALUE ? 'foo');
+
+CREATE FUNCTION test2dom(fn text) RETURNS hstore_foo
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+return {'a': 1, fn: 'boo', 'c': None}
+$$;
+
+SELECT test2dom('foo');
+SELECT test2dom('bar'); -- fail
+
+
+-- test as part of prepare/execute
+CREATE FUNCTION test3() RETURNS void
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+rv = plpy.execute("SELECT 'aa=>bb, cc=>NULL'::hstore AS col1")
+assert(rv[0]["col1"] == {'aa': 'bb', 'cc': None})
+
+val = {'a': 1, 'b': 'boo', 'c': None}
+plan = plpy.prepare("SELECT $1::text AS col1", ["hstore"])
+rv = plpy.execute(plan, [val])
+assert(rv[0]["col1"] == '"a"=>"1", "b"=>"boo", "c"=>NULL')
+$$;
+
+SELECT test3();
+
+
+-- test trigger
+CREATE TABLE test1 (a int, b hstore);
+INSERT INTO test1 VALUES (1, 'aa=>bb, cc=>NULL');
+SELECT * FROM test1;
+
+CREATE FUNCTION test4() RETURNS trigger
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE hstore
+AS $$
+assert(TD["new"] == {'a': 1, 'b': {'aa': 'bb', 'cc': None}})
+if TD["new"]["a"] == 1:
+ TD["new"]["b"] = {'a': 1, 'b': 'boo', 'c': None}
+
+return "MODIFY"
+$$;
+
+CREATE TRIGGER test4 BEFORE UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE test4();
+
+UPDATE test1 SET a = a;
+SELECT * FROM test1;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-02 06:03:35 +0000