PosgtreSQL create function-CodePudding

I'am try to create function in but have 42601 error code (SQL Error [42601]: ERROR: syntax error at or near "return"). Try to return false and still have error.

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)

CodePudding user response：

You need some quoting (most people use $-quoteing) and drop the RETURN statement:

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
$$;

Usually a function that selects data from a table is labeled as volatile, not immutable.