postgresql PostgtreSQL创建函数

omqzjyyz  于 2023-01-12  发布在  PostgreSQL
关注(0)|答案(2)|浏览(144)

我尝试在中创建函数,但出现42601错误代码(SQL错误[42601]:错误:在“return”处或附近有语法错误)。尝试返回false,但仍有错误。

create or replace function patient_age_in_range(patient_id uuid, low integer, high integer) returns bool
    language sql 
    immutable 
    returns null on null input
    return (select date_part('year', age(birthday)) between low and high from patients where id = patient_id)
uttx8gqw

uttx8gqw1#

你需要一些引用(大多数人使用$-quoteing)并删除RETURN语句:

CREATE
    OR REPLACE FUNCTION patient_age_in_range(patient_id UUID, low INTEGER, high INTEGER)
    RETURNS BOOL
    LANGUAGE SQL
    IMMUTABLE -- are you sure?
    RETURNS NULL ON NULL INPUT
AS
$$
SELECT date_part('year', age(birthday)) 
  BETWEEN low AND high
FROM patients
WHERE ID = patient_id
$$;

通常,从表中选择数据的函数被标记为volatile,而不是immutable。

qhhrdooz

qhhrdooz2#

上述语句的语法不正确。需要使用开始和END关键字将语句括在函数体中。正确的语法为:

CREATE OR REPLACE FUNCTION patient_age_in_range(patient_id UUID, low INTEGER, high INTEGER)
RETURNS BOOLEAN
LANGUAGE SQL 
IMMUTABLE 
RETURNS NULL ON NULL INPUT
BEGIN 
    RETURN (SELECT date_part('year', age(birthday)) BETWEEN low AND high FROM patients WHERE id = patient_id);
END;

相关问题