Returning boolean from SQL function after INSERT command

Question

I have a simple function for updating a column in a table. I want to return boolean to indicate if the update was successful or not.

Here is the function:

CREATE OR REPLACE FUNCTION api.file_confirm_upload_to_s3(_file_guid uuid )
RETURNS bool AS 
$BODY$
  UPDATE main.file
  SET is_uploaded_to_s3 = true
  WHERE guid = _file_guid
  RETURNING CASE WHEN guid IS NULL THEN false ELSE true END
$BODY$
  LANGUAGE SQL
  VOLATILE
  SECURITY DEFINER;

The function returns TRUE after a successful update, but it returns NULL after an unsuccessful update. Null is falsey, so it'll work for me, but I do wonder how I can solve this a bit cleaner.

An unsuccessful update returns an empty dataset, which yields NULL, hence the return value. How can I test for empty vs. non empty dataset instead of checking the value of the guid field?

Edit after JGH's answer.. Here is the code that does what I want. I had to switch to plpgsql to get access to the FOUND variable.

CREATE OR REPLACE FUNCTION api.file_confirm_upload_to_s3(_file_guid uuid )
RETURNS bool AS 
$BODY$
BEGIN
  UPDATE main.file
  SET is_uploaded_to_s3 = true
  WHERE guid = _file_guid;

  RETURN FOUND;
END;
$BODY$
  LANGUAGE plpgsql
  VOLATILE
  SECURITY DEFINER;

JGH · Accepted Answer · 2017-06-27 12:11:04Z

4

Just use return found; This special variable contains a boolean indicating whether one or more row was found using the previous query. Read its doc 41.5.5 here and example 41-2 there.

answered Jun 27, 2017 at 12:11

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

1 Comment

Michael Over a year ago

Thank you. I had to switch to plpgsql to get access to the found variable. I updated my question with the final code for others to find.

Oto Shavadze · Accepted Answer · 2017-06-27 12:26:49Z

2

Also you can do this in SQL mode

...
with c as( -- put your update into cte
    UPDATE main.file
    SET is_uploaded_to_s3 = true
    WHERE guid = _file_guid
    RETURNING guid 
)
select exists(select guid from c);
...

answered Jun 27, 2017 at 12:26

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

1 Comment

Michael Over a year ago

Thanks. I like the cleaner plpgsql solution better. But I'll keep this trick in mind.

Collectives™ on Stack Overflow

Returning boolean from SQL function after INSERT command

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related