How to Use quote_ident and quote_literal Inside a plpython Defined Function

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Functions fail when quote_ident 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_ident 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"

Environment

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_ident 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)