CODEWITHSUNDEEP
plpgsql
S. Yacko
S. Yacko

Reputation: 682

Having RETURN issue in PLPGSQL

I have created this store procedure to return a row_version if the organization id is in the database. 

CREATE OR REPLACE FUNCTION sote.validate_row_version(a BIGINT, b BIGINT) 
RETURNS text AS $$
DECLARE
  ret RECORD;
  v_error_stack text;
BEGIN
  SELECT row_version INTO ret
  FROM sote.organizations
  WHERE organization_id = a;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE EXCEPTION 'Organization Id (%) was not found', myOrganizationID;
      GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;
      RETURN to_json(v_error_stack);
  RETURN '0';
END;$$ LANGUAGE plpgsql;

When I run this command,

SELECT sote.validate_row_version(2,4);

I get the following error. I don't understand why. Any help would be great.

ERROR: control reached end of function without RETURN

Upvotes: 0

Views: 1044

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You need to move the RETURN '0' so it's executed before the EXCEPTION handler:

CREATE OR REPLACE FUNCTION sote.validate_row_version(a BIGINT, b BIGINT) 
RETURNS text AS $$
DECLARE
  ret RECORD;
  v_error_stack text;
BEGIN
  SELECT row_version INTO ret
  FROM sote.organizations
  WHERE organization_id = a;

  RETURN '0';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE EXCEPTION 'Organization Id (%) was not found', myOrganizationID;
    GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;
    RETURN to_json(v_error_stack);
END;$$ LANGUAGE plpgsql;

The code below EXCEPTION is all part of the exception block and won't be executed unless an exception occurs.

Best of luck.

Upvotes: 1

twin
twin

Reputation: 1669

There are 2 problems:

  • your select statement will not raise an exception unless you specify SELECT ... INTO STRICT

  • your RETURN '0' statement is part of the exception handler and will never be executed.

So you can rewrite your function as

CREATE OR REPLACE FUNCTION sote.validate_row_version(a BIGINT, b BIGINT) 
RETURNS text AS $$
DECLARE
  ret RECORD;
  v_error_stack text;
BEGIN
  SELECT row_version INTO STRICT ret
  FROM sote.organizations
  WHERE organization_id = a;
  RETURN '0';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE EXCEPTION 'Organization Id (%) was not found',   myOrganizationID;
      GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;
      RETURN to_json(v_error_stack);

END;$$ LANGUAGE plpgsql;

Upvotes: 2

Related Questions