Calling an Update function in postgresql

Question

I am trying to create a function to update the password of an user. Here is my function.

CREATE OR REPLACE FUNCTION update_user(userid int, upassword text) RETURNS integer AS  $$
    BEGIN
 UPDATE tbl_user 
     SET password= upassword 
 WHERE user_id = userid;
 SELECT 1;
 END;
 $$
 LANGUAGE plpgsql;

And I tried to execute this functions using.

SELECT update_user(1, 'test');

I got an error as

ERROR:  control reached end of function without RETURN CONTEXT:  PL/pgSQL function

I'm not sure where I go wrong. Please help me on this. I'm new to creating functions. Thanks in advance.

You say the function RETURNS integer, but you don't tell it what to return. What do you expect it to do? — Craig Ringer
– Craig Ringer, Commented Mar 27, 2014 at 3:47
Thanks, I have added "SELECT 1;" before "END;", the functions got execution and the password has been updated. Can you help me to capture the affected rows so that i would pass it a return value for validation. — saran
– saran, Commented Mar 27, 2014 at 3:56
It's in the manual - see GET DIAGNOSTICS. But I really don't understand why you're wrapping the statement in a function like this, rather than just doing that directly in the call site. — Craig Ringer
– Craig Ringer, Commented Mar 27, 2014 at 3:59

Patrick · Accepted Answer · 2014-03-27 03:42:28Z

3

Every PL/pgSQL function needs a RETURN statement. But in your case you can do with a SQL language function:

CREATE OR REPLACE FUNCTION update_user(userid int, upassword text) RETURNS void AS $$
  UPDATE tbl_user 
    SET password = upassword 
    WHERE user_id = userid;
$$ LANGUAGE sql;

Only when you need to do more elaborate processing (for instance, using local variables, processing logic, or a trigger) do you need a PL/pgSQL function.

answered Mar 27, 2014 at 3:42

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

1 Comment

Craig Ringer Over a year ago

Or just redefine your function as RETURNS VOID.

Collectives™ on Stack Overflow

Calling an Update function in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related