hstore_plpython.out

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"
-- A mapping whose items() raises should be reported as an error, not crash
-- the backend
CREATE FUNCTION test1broken() RETURNS hstore
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
class C(dict):
    def items(self):
        raise ValueError('items failed')
d = C()
d['x'] = 1
return d
$$;
SELECT test1broken();
ERROR:  could not get items from Python mapping
CONTEXT:  while creating return value
PL/Python function "test1broken"
-- Likewise for a mapping whose items() does not return key/value pairs
CREATE FUNCTION test1malformed() RETURNS hstore
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
class C(dict):
    def items(self):
        return [42]
d = C()
d['x'] = 1
return d
$$;
SELECT test1malformed();
ERROR:  items() of a Python mapping must return key/value pairs
CONTEXT:  while creating return value
PL/Python function "test1malformed"
-- Likewise for a mapping whose items() yields fewer pairs than its length
CREATE FUNCTION test1short() RETURNS hstore
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
class C(dict):
    def items(self):
        return []
d = C()
d['x'] = 1
return d
$$;
SELECT test1short();
ERROR:  items() of a Python mapping must return key/value pairs
CONTEXT:  while creating return value
PL/Python function "test1short"
-- Likewise for a mapping whose __len__() raises
CREATE FUNCTION test1brokenlen() RETURNS hstore
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
class C(dict):
    def __len__(self):
        raise ValueError('len failed')
d = C()
d['x'] = 1
return d
$$;
SELECT test1brokenlen();
ERROR:  could not get size of Python mapping
CONTEXT:  while creating return value
PL/Python function "test1brokenlen"
-- 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)