python - 带有 hstore 字符串文字 FAIL 的 postgres plpy.execute

Question

我正在使用 postgresql 9.2 编写一个 plpython 函数。假设代码已经执行了一个返回 hstore 字符串的查询。然后我想发出一个查询:

SELECT hstore_to_matrix('hstorestring')

假设它是一个包含 hstore 字符串的字符串:A=>B

create or replace function testfreq() 
returns text
as $$
hstorestring = '"GT"=>"thing","HS"=>"[-0.1,-0.2]"'
rv2 = plpy.execute("SELECT hstore_to_matrix(%s)" % (plpy.quote_literal(hstorestring)))
plpy.log("Hstore:",rv2[0])
return("done")
$$ LANGUAGE plpythonu;

运行方式

select testfreq();

返回

testdb=# select testfreq();
ERROR:  plpy.Error: unrecognized error in PLy_spi_execute_fetch_result
CONTEXT:  Traceback (most recent call last):
PL/Python function "testfreq", line 3, in <module>
rv2 = plpy.execute("SELECT hstore_to_matrix(%s)" % (plpy.quote_literal(hstorestring)))
PL/Python function "testfreq":  

如果在上面的代码中替换为 hstore_to_array，则输出为:

testdb=# select testfreq();
LOG:  ('Hstore:', {'hstore_to_array': ['GT', 'thing', 'HS', '[-0.1,-0.2]']})
CONTEXT:  PL/Python function "testfreq"
testfreq 
----------
done
(1 row)

我还尝试使用 hstore 运算符而不是函数，并且我在 pgsql 终端中尝试了这些函数，以确保它们在未嵌入 python 时也能正常工作。任何指针将不胜感激。

Answer 1

看起来 PL/Python 不能正确处理多维数组:

create or replace function testarray()
returns text
as $$
rv2 = plpy.execute("SELECT ARRAY[ ARRAY['1','2','3'], ARRAY['a','b','c'] ];" )
$$ LANGUAGE plpythonu;

结果:

craig=# select testarray();
ERROR:  plpy.Error: unrecognized error in PLy_spi_execute_fetch_result
CONTEXT:  Traceback (most recent call last):
  PL/Python function "testarray", line 2, in <module>
    rv2 = plpy.execute("SELECT ARRAY[ ARRAY['1','2','3'], ARRAY['a','b','c'] ];" )
PL/Python function "testarray"

(在 Pg 9.2.4、Python 2.7.3 上测试)。

hstore 文本有效:

craig=# SELECT '"GT"=>"thing","HS"=>"[-0.1,-0.2]"'::hstore;
               hstore               
------------------------------------
 "GT"=>"thing", "HS"=>"[-0.1,-0.2]"
(1 row)

并且查询在 PL/Python 之外工作:

craig=# select hstore_to_matrix('"GT"=>"thing","HS"=>"[-0.1,-0.2]"');
        hstore_to_matrix         
---------------------------------
 {{GT,thing},{HS,"[-0.1,-0.2]"}}
(1 row)

进一步表明这是一个 PL/Python 问题。

您可以通过转换为 text 然后在返回结果后转换回 text[] 来解决这个问题，尽管它效率低下:

create or replace function testfreq() 
returns text
as $$
hstorestring = '"GT"=>"thing","HS"=>"[-0.1,-0.2]"'
rv2 = plpy.execute("SELECT hstore_to_matrix(%s)::text" % (plpy.quote_literal(hstorestring)))
plpy.log("Hstore:",rv2[0])                                                                                                                                                     
return("done")                                                                                                                                                                 
$$ LANGUAGE plpythonu; 

结果:

craig=# SELECT testfreq();
 testfreq 
----------
 done
(1 row)