Обсуждение: Raise exception without using plpgsql?
Hi,
Is there a way to raise an exception with a message,
without having to add your own plpgsql helper-function?
Currently this is what I have:
CREATE OR REPLACE FUNCTION raise(message text, debug json, dummy_return_value anyelement)
RETURNS anyelement
LANGUAGE plpgsql
AS $$
BEGIN
RAISE '% %', message, debug;
END;
$$;
The dummy value allows using the function in a context where a value of the given type is expected.
Here is a mockup example on the coding pattern where I typically would use this raise() helper-function:
SELECT
CASE
WHEN foo = 0 THEN f0(bar)::baz
WHEN foo = 1 THEN f1(bar)::baz
WHEN foo = 2 THEN f2(bar)::baz
ELSE raise('Not implemented',json_build_object(
'foo',foo,
'bar',bar
),NULL::baz)
END
FROM ...
The idea is to throw an exception when a case is not handled, instead of just letting the CASE produce a NULL value.
The dummy_return_value anyelement with the same type as the other CASEs is necessary, otherwise, if trying to just return anyelement without the dummy_return_value you would get error:
DETAIL: A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.
Is there any idiomatic way of achieving the same, without having to rely on a plpgsql function in this way?
Best regards,
Joel
On Thursday, December 17, 2020, Joel Jacobson <joel@compiler.org> wrote:
Hi,Is there a way to raise an exception with a message,without having to add your own plpgsql helper-function?
Not that i’ve seen. There is no SQL way to do so, and I’ve not seen a core c-language function that provides that feature.
David J.
On Thu, Dec 17, 2020 at 03:32:09PM +0100, Joel Jacobson wrote: > Hi, > Is there a way to raise an exception with a message, > without having to add your own plpgsql helper-function? > Currently this is what I have: Well, you can: DO $$ begin raise notice 'zz'; END; $$; It's cheating though, as it still needs plpgsql. Best regards, depesz
čt 17. 12. 2020 v 17:45 odesílatel hubert depesz lubaczewski <depesz@depesz.com> napsal:
On Thu, Dec 17, 2020 at 03:32:09PM +0100, Joel Jacobson wrote:
> Hi,
> Is there a way to raise an exception with a message,
> without having to add your own plpgsql helper-function?
> Currently this is what I have:
Well, you can:
DO $$ begin raise notice 'zz'; END; $$;
It's cheating though, as it still needs plpgsql.
Best regards,
Some years there was discussion about this possibility
There was another thread, but I cannot to find it
Regards
Pavel
depesz