The only time I encountered a similar problem on SO is on here, but the proposed solution was not relevant for me.
I call a PostgreSQL function, but it’s returning a single value instead of one value per field, which I can access. I have a table "Words"
, which consists of a primary key word
and other fields.
This is the function:
CREATE OR REPLACE FUNCTION equal(s TEXT) RETURNS "Word" AS $$ SELECT * FROM "Word" WHERE word = s; $$ LANGUAGE sql;
And this is the function call (using SQLAlchemy):
result = db.session.query(func.equal("is")).first()
Output would be: '(is, ... )'
instead of the desired output: ('is', ...)
.
How can I adjust the function so it returns the tuple instead?
Follow-up question
As suggested, db.session.execute(f"SELECT * FROM equal('{w}');").first()
does the trick. Unfortunately it returns a list of None
‘s if no result is found. I’d like it to return None
instead. The query
object in my original approach does this, but returns the output as a string.
Advertisement
Answer
Adjust the function call, not the function.
To decompose the returned (composite) row type and get individual columns, call the function with:
SELECT * FROM equal('is');
instead of:
SELECT equal('is');
See:
To return nothing instead of a row of NULL values when no row is found, make it a set-returning function:
CREATE OR REPLACE FUNCTION equal(_s text) RETURNS SETOF "Word" -- ! LANGUAGE sql AS $$ SELECT * FROM "Word" WHERE word = _s; $$;
db<>fiddle here
Same call.
The
SETOF
modifier indicates that the function will return a set of items, rather than a single item.
So it can also return an empty set, i.e. nothing.