"missing FROM-clause entry" in PLPGSQL function to update a table

Question

I am trying to update a table in Postgres, but I get below error.
Userid is the primary key.

CREATE OR REPLACE FUNCTION public.update_user(username character varying, 
                                                userid character varying, 
                                                roleid integer,
                                                deptid integer, 
                                                status integer)
RETURNS integer AS 
$$
BEGIN

UPDATE public."M_User" SET public."M_User".user_name = public.update_user.username, 
    public."M_User".user_role_id = public.update_user.roleid, 
    public."M_User".dept_id = public.update_user.deptid, 
    public."M_User".status = public.update_user.status 
    WHERE public."M_User".user_id = userid;
    
RETURN 0;
END
$$
LANGUAGE 'plpgsql';

Call:

SELECT * FROM public.update_user ('ji','gis', 123, 24, 1);

Error:

ERROR:  missing FROM-clause entry for table "update_user"
LINE 1: ...E public."M_User" SET public."M_User".user_name = public.upd...
                                                             ^
QUERY:  UPDATE public."M_User" SET public."M_User".user_name = public.update_user.username, 
  public."M_User".user_role_id = public.update_user.roleid, 
  public."M_User".dept_id = public.update_user.deptid, 
  public."M_User".status = public.update_user.status 
  WHERE public."M_User".user_id = userid
CONTEXT:  PL/pgSQL function update_user(character varying,character varying,integer,integer,integer) line 4 at SQL

statement SQL state: 42P01

How to resolve the issue?

Answer 1

CREATE OR REPLACE FUNCTION public.update_user(_username varchar, 
                                                _userid varchar, 
                                                _roleid integer,
                                                _deptid integer, 
                                                _status integer)
  RETURNS integer
  LANGUAGE sql AS 
$func$
UPDATE public."M_User" u
SET    user_name    = _username
     , user_role_id = _roleid
     , dept_id      = _deptid
     , status       = _status 
WHERE  u.user_id = _userid
AND   (u.user_name, u.user_role_id, u.dept_id, u.status) IS DISTINCT FROM
      ( _username ,       _roleid ,  _deptid ,  _status)  -- optional addition
RETURNING u.user_id;
$func$

You can qualify function parameters with the function name to disambiguate, but that's typically an awkward solution. Breaks when renaming the function. There are other ways. See:

• PL/pgSQL column name the same as variable

I like to prefix parameters and variables with an underscore, and never do the same for column names. Not standard, but a widespread convention. Make it a habit to table-qualify all column names, and you are safe. A table alias helps to keep the noise down. Basics in the manual on UPDATE.

But target columns in an UPDATE statement cannot be qualified. (They are never ambiguous.)

I added an optional predicate to skip the UPDATE if it wouldn't change anything. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

You do not need PL/pgSQL for this. (Though you can, of course.) A simpler SQL function does the job. See:

• Difference between language sql and language plpgsql in PostgreSQL functions

RETURN 0 does nothing useful. I changed it to return the effective user_id of the updated row. (Typically the same as the input _userid, but it might differ with triggers.) The important difference: you only get the return value if a row is actually updated, making it useful. Else, you might as well declare the function as RETURNS void. Just as useful as an unconditional RETURN 0;, but cheaper.

Answer 2

The doc says one can refer to an input as function_name.parameter_name, so try removing references to the schema_name

UPDATE public."M_User" 
SET user_name = update_user.username, 
    user_role_id = update_user.roleid, 
    dept_id = update_user.deptid, 
    status = update_user.status 
WHERE user_id = update_user.userid;