postgres plpy.execute with hstore string literal FAIL

Question

I'm writing a plpython function using postgresql 9.2. Assume the code has already executed a query that returns hstore strings. I wish to then issue a query:

SELECT hstore_to_matrix('hstorestring')

Let's say that it is a string containing the hstore string: 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;

Run as

select testfreq();

Returns

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":

If you replace with hstore_to_array in the code above, the output is:

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

I have also tried to use hstore operators instead of functions and I have tried out these functions in a pgsql terminal to make sure they work when not embedded in python. Any pointers would be greatly appreciated.

The string literal may be a red herring. I get the same error with any "SELECT yourdbfunc()" query string. — user2288586, Apr 17 '13 at 00:30
Another update. I can execute plpy.execute("SELECT hstore_to_array(%s)" % (ploy.quote_literal(s))). But not hstore_to_matrix. Yay for bustedness. — user2288586, Apr 17 '13 at 00:47
Please edit your question to include a *complete* example of a function that demonstrates the problem, from `CREATE FUNCTION` onwards. I'd also like to see your Python version if possible. Comment here when done so I get notified please. — Craig Ringer, Apr 17 '13 at 04:19

score 2 · Answer 1 · answered Apr 18 '13 at 00:36

It looks like PL/Python doesn't correctly handle multi-dimensional arrays:

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

Result:

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"

(Tested on Pg 9.2.4, Python 2.7.3).

The hstore text is valid:

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

and the query works outside 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)

further suggesting that this is a PL/Python issue.

You can probably work around this by casting to text then casting back to text[] after returning the result, though it's inefficient:

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;

Result:

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

postgres plpy.execute with hstore string literal FAIL

1 Answers1