sqlstate.sql

--
-- Error model: how Ruby exceptions and PostgreSQL errors interact, what is
-- preserved when a database error is caught, and what is not.
--

-- A specific exception class's message is preserved when it propagates out.
CREATE FUNCTION es_custom() RETURNS int LANGUAGE plruby AS $$
    raise ArgumentError, 'custom failure'
$$;
SELECT es_custom();

-- A caught database error: it is a StandardError, its message names the
-- offending object, its SQLSTATE is exposed, and the session keeps working
-- afterwards.
CREATE FUNCTION es_caught() RETURNS text LANGUAGE plruby AS $$
    begin
        spi_exec('select * from missing_relation_xyz')
        'no error'
    rescue => e
        "std=#{e.is_a?(StandardError)} names_it=#{e.message.include?('missing_relation_xyz')} sqlstate=#{e.sqlstate}"
    end
$$;
SELECT es_caught();

-- The SQLSTATE reflects the error class (division_by_zero here).  A
-- PLRuby::Error not backed by a database error (elog) has a nil sqlstate,
-- and an ordinary Ruby exception has no sqlstate method at all.
CREATE FUNCTION es_sqlstate() RETURNS text LANGUAGE plruby AS $$
    db = begin
             spi_exec('select 1 / 0')
             'no error'
         rescue => e
             e.sqlstate
         end
    pl = begin
             elog('ERROR', 'synthetic')
         rescue PLRuby::Error => e
             e.sqlstate.inspect
         end
    rb = begin
             raise 'plain ruby error'
         rescue => e
             e.respond_to?(:sqlstate) ? 'has method' : 'no method'
         end
    "db=#{db} elog=#{pl} ruby=#{rb}"
$$;
SELECT es_sqlstate();

-- An ensure block runs on both the success and the rescue path.
CREATE FUNCTION es_ensure(boolean) RETURNS text LANGUAGE plruby AS $$
    log = []
    r = begin
            raise 'boom' if args[0]
            'ok'
        rescue
            'rescued'
        ensure
            log << 'ensured'
        end
    "#{r}:#{log.join}"
$$;
SELECT es_ensure(false);
SELECT es_ensure(true);

-- pg_raise can attach DETAIL, HINT, and a specific SQLSTATE...
CREATE FUNCTION es_structured() RETURNS void LANGUAGE plruby AS $$
    pg_raise('ERROR', 'order rejected',
             detail: 'order 42 exceeds the credit limit',
             hint: 'raise the limit or split the order',
             sqlstate: 'P0001')
$$;
SELECT es_structured();

-- ...and a caller that rescues the resulting database error sees all three.
CREATE FUNCTION es_catch_structured() RETURNS text LANGUAGE plruby AS $$
    begin
        spi_exec('select es_structured()')
        'no error'
    rescue PLRuby::Error => e
        "state=#{e.sqlstate} detail=#{e.detail} hint=#{e.hint}"
    end
$$;
SELECT es_catch_structured();

-- Non-error levels emit the fields directly.
CREATE FUNCTION es_notice_fields() RETURNS void LANGUAGE plruby AS $$
    pg_raise('NOTICE', 'heads up', detail: 'the details', hint: 'the hint')
$$;
SELECT es_notice_fields();

-- An invalid sqlstate is rejected at the call.
CREATE FUNCTION es_badstate() RETURNS void LANGUAGE plruby AS $$
    pg_raise('ERROR', 'nope', sqlstate: 'abc')
$$;
SELECT es_badstate();

-- pg_raise and elog both reject an unknown level with a PL/Ruby error.
CREATE FUNCTION es_badraise() RETURNS void LANGUAGE plruby AS $$
    pg_raise('BOGUS', 'nope')
$$;
SELECT es_badraise();
CREATE FUNCTION es_badelog() RETURNS void LANGUAGE plruby AS $$
    elog('BOGUS', 'nope')
$$;
SELECT es_badelog();