[SOLVED] Why psycopg cursor.fecthone return a list of None if no row was hit from select query?

Why psycopg cursor.fecthone return a list of None if no row was hit from select query?

My function to query account by email:

CREATE FUNCTION get_account_by_email(account_email varchar(64)) RETURNS account AS $$
SELECT id,
    name,
    email
FROM account
WHERE account.email = account_email;
$$ LANGUAGE SQL;

My python code to call the function ("psycopg[binary]"==3.1.9):

async def get_account_by_email(self, email: str):
    async with self.pool.connection() as conn:
        resp = await conn.execute(f"SELECT * FROM get_account_by_email(%s);", (email,))
        print(resp.rowcount)
        print(resp)
        return await resp.fetchone()

I tried query use an email that doesn't exist. But I get following result: The row count returned is 1. The fetchone returned: (None, None, None, None, None, None)

It is non sense.

Solution

You declared a non-set-returning function. It always returns exactly one value of type "account". If the query in the function finds no rows, then the function returns NULL as that value.

If you want your function to be able to return either more than 1 row or less than 1 row then you need to declare that it returns a TABLE or a SETOF.