Database Reference
In-Depth Information
AS $$
return 'PostgreSQL %s.' % txtdata
$$ LANGUAGE plpython3u;
The result can be seen using the following statement:
warehouse_db=# SELECT python_string('rocks');
python_string
-----------------
PostgreSQL rocks
(1 row)
Accessing the database with PL/Python
We will now access the database through PL/Python using the
plpy.execute()
function that will take a
text
query as input and return a list of dictionaries as the
result. The
warehouse_id
database will be sent as an argument to the
getRecords_
withpython()
function that is further passed to
plpy.execute()
.This can be done
in the following manner:
warehouse_db=# CREATE OR REPLACE FUNCTION
getRecords_withpython(warehouse_id int)
RETURNS text
AS $$
res = plpy.execute("""
SELECT * FROM warehouse_tbl
WHERE warehouse_id = '%s'""" % warehouse_id)
if res.nrows() > 0:
return res
else:
return 'No rows found'
$$ LANGUAGE plpython3u;
Now, execute the code in
psql
using the following statement:
warehouse_db=# SELECT getRecords_withpython(1);
getRecords_withpython
-------------------------------------------------------
<PLyResult status = 5 nrows = 1 rows = [{'city': 'New London',
'zip': '4321', 'year_created': 2009, 'warehouse_id': 1, 'state':
'CT', 'warehouse_name': 'Mark Corp', 'street_address': '207-F
Main Service Road East'}]>
(1 row)
