Pivotal Knowledge Base

Follow

How to use quote_indent and quote_literal inside a plpython defined function

Environment

Pivotal Greenplum: 4.3.x

OS: RHEL 6.x

Symptom

Functions fail when quote_indent or quote_literal functions are called from within plpy. The functions can be created without errors but when they are run, and the python code compiles and executes, an error message shows up, and the query execution fails.

Error Message:

Calling either quote_literal or quote_indent from within a plpy function will generate the following error message

gpadmin=# CREATE OR REPLACE FUNCTION plpytest.test1(value text) 
RETURNS text AS $BODY$ import plpy plpy.quote_literal(value) return value $BODY$ 
LANGUAGE plpythonu VOLATILE;
gpadmin=# select plpytest.test1('A''B'); ********** Error ********** ERROR: AttributeError: 'module' object has no attribute 'quote_literal' (plpython.c:4648) CONTEXT: Traceback (most recent call last): 
PL/Python function "test1", line 5, in <module> plpy.quote_literal(value) PL/Python function "test1"

Cause

These plpython utility functions were not defined in PostgreSQL database until version 9.1. Greenplum 4.3.X uses PostgreSQL 8.2 code, so these two utility functions are not accessible directly from plpython. 

Resolution

To workaround this, a prepared statement can be used from within plpy and use the quote_literal or quote_indent functions normally

gpadmin=# CREATE OR REPLACE FUNCTION plpytest.test2(value text)
RETURNS text AS
$BODY$
import plpy
myquery = plpy.prepare("SELECT quote_literal($1)",["text"])
myresult = plpy.execute(ql_query,[value])[0]
return value
$BODY$
LANGUAGE plpythonu VOLATILE;
gpadmin=# select plpytest.test2('A''B');  test2
-------
A'B
(1 row)

Comments

Powered by Zendesk