Function returns row type as single value instead of one value per field

Question

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.

Answer 1

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:

• Simple PostgreSQL function to return rows

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 manual:

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.