postgres,定义一个常量(或函数)和一个数值,用于检查约束比较

yk9xbfzb  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(253)

我尝试创建一个“常量”或“返回常量的函数”,用于检查约束比较,例如:

create or replace function max_num() returns numeric language sql immutable parallel safe as
    133137223119940041836849736350226675204;

create table thing (
  amount numeric(39) not null check (amount <= max_num())
)

显然这不是有效的psql,所以我希望有人能告诉我如何在postgres中达到相同或相似的效果?我希望它出现在常量中的原因是,我需要对多个列执行此检查约束。
psql版本: psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

cpjpxq1n

cpjpxq1n1#

在postgresql 12中,函数不需要有参数:

select version();
                                                 version                     

-----------------------------------------------------------------------------
----------------------------
 PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
 (Red Hat 4.8.5-39), 64-bit
(1 row)

CREATE OR REPLACE FUNCTION max_num()
RETURNS numeric
LANGUAGE sql
IMMUTABLE PARALLEL SAFE AS
$func$
    SELECT 13::numeric;
$func$
        ;
CREATE FUNCTION

CREATE TABLE THING (
  amount numeric(39) NOT NULL CHECK (amount < max_num())
);
CREATE TABLE

INSERT INTO thing(amount) VALUES ( 12);
INSERT 0 1

INSERT INTO thing(amount) VALUES ( 14);
ERROR:  new row for relation "thing" violates check constraint "thing_amount_check"
DETAIL:  Failing row contains (14).

相关问题