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

Question

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?

Read Variable substitution.

Adrian Klaver
– Adrian Klaver

2024-04-07 16:22:18 +00:00
Commented Apr 7, 2024 at 16:22 — Adrian Klaver
– Adrian Klaver, Commented Apr 7, 2024 at 16:22

Erwin Brandstetter · Accepted Answer · 2024-04-07 01:25:48Z

1

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:

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.

edited Apr 7, 2024 at 1:25

answered Apr 7, 2024 at 0:54

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tristan Over a year ago

"If you need a function for this at all." For my day job we use MS SQL Server, and have SPs for every table interaction. My plan was to do the same here. Is that not a recommended pattern for postgres?

Erwin Brandstetter Over a year ago

It all depends. For a simple SELECT, I don't see much possible gain, but much overhead.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related