Why is the explicit table name necessary in this Postgres Function?

I'm using Postgres for the first time and am confused by an "ambiguous" error regarding my functions.

CREATE TABLE "user" (
    user_id VARCHAR(40) PRIMARY KEY,
    email VARCHAR(254),
    phone_number VARCHAR(15) NOT NULL,
    username VARCHAR(15) NOT NULL,
    display_name VARCHAR(15),
    rank_points INT NOT NULL,
    created_on TIMESTAMP DEFAULT (TIMEZONE('UTC', NOW()))
);

I have the following function to get users by id, phone, or username.

CREATE OR REPLACE FUNCTION user_get(user_id_param VARCHAR(40) DEFAULT NULL, phone_number_param VARCHAR(15) DEFAULT NULL, username_param VARCHAR(15) DEFAULT NULL)
RETURNS TABLE(user_id VARCHAR(40), email VARCHAR(254), phone_number VARCHAR(15), username VARCHAR(15), display_name VARCHAR(15), rank_points INT, created_on TIMESTAMP) AS
$$
BEGIN
    RETURN QUERY
    SELECT "user".user_id, "user".email, "user".phone_number, "user".username, "user".display_name, "user".rank_points, "user".created_on
    FROM "user"
    WHERE 
    (user_id_param IS NOT NULL AND "user".user_id = user_id_param) OR
    (phone_number_param IS NOT NULL AND "user".phone_number = phone_number_param) OR
    (username_param IS NOT NULL AND "user".username = username_param);
END;
$$
LANGUAGE plpgsql;

My question is regarding the requirement of "user." in the SELECT as well as the WHERE cause. Without it I get an error:

ERROR: column reference "user_id" is ambiguous

Where is the ambiguity? Is it with the user_id in the RETURNS TABLE clause?

Where is the ambiguity? Is it with the user_id in the RETURNS TABLE clause?

Yes, all columns listed there count as OUT parameters, which are visible in any SQL query inside the function - so conflict with column names, unless qualified. See:

• How to return result of a SELECT inside a function in PostgreSQL?
• Scope of column names, aliases, and OUT parameters in PL/pgSQL function

That said, your function can be much simpler:

CREATE OR REPLACE FUNCTION user_get(user_id_param text = NULL, phone_number_param text = NULL, username_param text = NULL)
  RETURNS SETOF "user"
  LANGUAGE sql AS
$func$
SELECT *
FROM   "user" u
WHERE  u.user_id = user_id_param
   OR  u.phone_number = phone_number_param
   OR  u.username = username_param;
$func$;

A simpler SQL function does it. If you need a function for this at all.

Your return type matches the row type of the table exactly, so you can use that. RETURNS SETOF "user" instead of RETURNS TABLE (...) (Note that this introduces a functional dependency on the table type!) This also removes the observed conflicts. And this is one of the rare cases where SELECT * makes sense when returning from a function, since the return type matches the row type per definition, literally.

If you have to table-qualify column names (which is never a bad idea to begin with!) consider a short column alias for readability (u in my case).

I doubt you need the default value NULL for IN parameters.

And you don't need to check for null additionally. Checking with the equality operator = rules out null implicitly, as null compared to anything returns null, never true. See:

• Best way to check for "empty or null value"

Also, just don't use a completely reserved word like user as identifier.