I have this assignment in plpythonu in PostgreSQL function:
result=plpy.execute("select value from table where doctitle like '%%%s%%'"%projectname)
if result:
final = result[0]["value"]
The query can return 1 text result or nothing.
The issue is that if the query returned nothing the if result: condition is still true (Cuz it contains the value column)... I want the assignment to final happen only if there is actual value in value column. How do I check for that?
The result object emulates a list or dictionary object. Check its length:
if len(result) > 0:
But your test should work as an empty list or dictionary evaluates as False:
create or replace function p()
returns boolean as $$
result = plpy.execute('select 1 as i where false')
if result: return True
else: return False
$$ language plpythonu;
Returns false:
select p();
p
---
f
Pass the parameters to execute in instead of substitute yourself. Otherwise you will vulnerable to SQL injection:
query = """
select value
from table
where doctitle like format('%%%s%%', $1)
"""
plan = plpy.prepare(query, ["text"])
result = plpy.execute(plan, [_text_parameter])
if result:
final = result[0]["value"]
https://www.postgresql.org/docs/current/static/plpython-database.html
