LANGUAGE sql中函数的RAISE EXCEPTION语句的等效或替代方法? [英] Equivalent or alternative method for RAISE EXCEPTION statement for function in LANGUAGE sql?

问题描述

对于下面以 LANGUAGE sql 编写的函数的 RAISE EXCEPTION 语句是否有等效(或解决方法)?

Is there an equivalent (or workaround) for the RAISE EXCEPTION statement for the function written below in LANGUAGE sql?

CREATE OR REPLACE FUNCTION fn_interpolation (p_yearinteger integer, 
p_admin_id integer, p_crop_id integer, p_cropparameter integer)

RETURNS TABLE (value double precision, remark text)

AS $$

WITH

yearvalues AS (SELECT yearinteger, value FROM cropvalues WHERE crops_id = 
p_crop_id AND admin_id = p_admin_id AND parameter_id = p_cropparameter),

如果输入到函数中的参数不存在，我需要该函数中止并返回错误消息.例如IF parameter_id != p_cropparameter THEN RAISE EXCEPTION ‘无效的cropparameter’END IF

I need the function to abort and to RETURN an error message if the arguments entered into the function do not exist. e.g. IF parameter_id != p_cropparameter THEN RAISE EXCEPTION ‘invalid cropparameter’ END IF

推荐答案

只需定义一个简单的包装函数即可.

Just define a trivial wrapper function.

CREATE OR REPLACE FUNCTION raise_exception(text) RETURNS text AS $$
BEGIN
    RAISE EXCEPTION '%',$1;
END;
$$ LANGUAGE plpgsql VOLATILE;

然后使用CASE:

SELECT CASE 
         WHEN parameter_id != p_cropparameter 
           THEN raise_exception("blah") 
         ELSE parameter_id 
       END;

这仅在 CASE 否则返回 text 时才有效，例如如果 parameter_id 是整数，你得到:

This only works if the CASE otherwise returns text though, e.g. if parameter_id is integer you get:

regress=> SELECT CASE WHEN 1 = 2 THEN raise_exception('blah') ELSE 1 END;
ERROR:  CASE types integer and text cannot be matched
LINE 1: SELECT CASE WHEN 1 = 2 THEN raise_exception('blah') ELSE 1 E...

您可以通过使用多态函数的 hack 来解决此问题.定义:

You can work around this with a hack using polymorphic functions. Define:

CREATE OR REPLACE FUNCTION raise_exception(anyelement, text) RETURNS anyelement AS $$
BEGIN
    RAISE EXCEPTION '%',$2;
    RETURN $1;
END;
$$ LANGUAGE plpgsql VOLATILE;

然后将 case 类型的假值传递给它，以便 PostgreSQL 正确类型匹配它，例如

then pass a fake value of the case type to it so PostgreSQL type-matches it correctly, e.g.

SELECT CASE WHEN 1 = 1 THEN raise_exception(0, 'blah') ELSE 1 END;

或

SELECT CASE WHEN 1 = 1 THEN raise_exception(NULL::integer, 'blah') ELSE 1 END;

看起来太难了?那是因为实际上，这种事情通常在 PL/PgSQL 中做得更好.

All seem too hard? That's because really, this sort of thing is usually just better done in PL/PgSQL.

这篇关于LANGUAGE sql中函数的RAISE EXCEPTION语句的等效或替代方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！