postgresql - 从函数返回记录

Question

我正在尝试使用非常简单的测试函数在 plpgsql 中返回多个值。测试函数将一个整数作为输入，如果它是正数、负数和零则应该返回。我看过一些例子，但它们太复杂了，让我难以理解。

这是我尝试过的:

create or replace function test(v integer)
returns record as $$

BEGIN

    IF v > 0
        THEN return true as positive, false as negative, false as zero;
    ELSEIF v < 0
        THEN return false as positive, true as negative, false as zero;
    ELSE 
        return false as positive, false as negative, true as zero;
    END IF;

END;
$$ LANGUAGE plpgsql;

也试过这个

create or replace function test(v integer)
returns record as $$

DECLARE 
    result record;

BEGIN

    IF v > 0
        THEN 
            result.positive = true;
            result.negative = false;
            result.zero = false;

    ELSEIF v < 0
        THEN 
            result.positive = false;
            result.negative = true;
            result.zero = false;
    ELSE 
            result.positive = false;
            result.negative = false;
            result.zero = true;
    return result;
    END IF;

END;
$$ LANGUAGE plpgsql;

还有这个:

IF v > 0
    THEN 
        SELECT true, false, false into
        result.positive, result.negative, result.zero;

ELSEIF v < 0
    THEN 
        SELECT false, true, false into
        result.positive, result.negative, result.zero;
ELSE 
        SELECT false, false, true into
        result.positive, result.negative, result.zero;
return result;
END IF;

Answer 1

我会按如下方式解决问题:

CREATE TYPE sign AS ENUM ('negative', 'zero', 'positive');

CREATE FUNCTION test(integer) RETURNS sign
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE
            WHEN $1 < 0
            THEN 'negative'
            WHEN $1 > 0
            THEN 'positive'
            ELSE 'zero'
         END::sign$$;

如果您想像示例中那样返回一个record，则必须使用ROW() 构造函数从三个 bool 值:

RETURN ROW(true, false, false);

但这种解决方案有其缺点:您无法在 SQL 中访问复合类型的各个字段，因为 PostgreSQL 在解析时不知道这些字段:

test=> SELECT test(42);
┌─────────┐
│  test   │
├─────────┤
│ (t,f,f) │
└─────────┘
(1 row)

test=> SELECT (test(42)).*;
ERROR:  record type has not been registered

test=> SELECT * FROM test(42);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM test(42);
                      ^

您必须在查询中指定记录定义:

test=> SELECT positive FROM test(42) test(positive boolean, negative boolean, zero boolean);
┌──────────┐
│ positive │
├──────────┤
│ t        │
└──────────┘
(1 row)

但这违背了您的可变记录目标。这就是为什么这些功能不如您引用的答案有用的原因。