CODEWITHSUNDEEP
postgresql
Michael
Michael

Reputation: 1864

Returning boolean from SQL function after INSERT command

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;

Upvotes: 3

Views: 3165

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42783

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);
...

Upvotes: 2

JGH
JGH

Reputation: 17866

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.

Upvotes: 4

Related Questions