Is "raise exception" the only way to return an error from pgplsql to node-postgres?

Question

Say, I have a pgplsql function (e.g. buyItem) which returns something (e.g. bought item parameters). In some cases I want to indicate that something went wrong so the result is empty (e.g. not enough money to buy desired item). It's just a usual result, I'd not call it an exception (and definitely would not print an error about it on the db server, as raise exception does).

So the question is: what's the best practice to handle such cases?

Answer 1

Your two options are either to raise an exception, or return a record with both success and error information:

CREATE OR REPLACE FUNCTION buyItem(itemId integer)
RETURNS RECORD
AS $$
DECLARE 
  result RECORD;
BEGIN
  IF itemId > 0 THEN
    SELECT 'purchase successful', NULL INTO result;
  ELSE
    SELECT NULL, 'purchase failed' INTO INTO result;
  END IF;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

There are more examples of returning records here: Return multiple fields as a record in PostgreSQL with PL/pgSQL