PostgreSQL函数CASE语法报错：'syntax error at or near CASE'求助

Got it, let's break down what's causing that syntax error and fix your function step by step.

The Root Issues #

Your original function has three key problems that trigger the syntax error:

1. SQL function structure requirements: SQL-language functions (marked with LANGUAGE sql) need to wrap their return logic in a valid SELECT statement. You started directly with CASE instead of embedding it within SELECT.
2. Redundant SELECT statements inside CASE: The CASE expression itself returns a value, so you don't need to add SELECT before each branch result. Those extra SELECTs break the syntax flow.
3. Unnecessary subquery in the WHEN condition: If getTotalPersons(num) is a scalar function (returns a single value), wrapping it in SELECT * FROM ... is unnecessary and can cause issues when checking IS NOT NULL.

Corrected Function Code #

Here's the fixed version of your function:

CREATE OR REPLACE FUNCTION getNumberOfPersons(num int) 
RETURNS numeric 
LANGUAGE sql AS $$
SELECT 
  CASE 
    WHEN getTotalPersons(num) IS NOT NULL THEN getPerson(num) + getTotalPersons(num) * getPerson(num)
    ELSE getPerson(num)
  END;
$$;

What Changed? #

• Wrapped the entire CASE expression inside a SELECT statement (required for SQL-language functions to return a single scalar value).
• Removed the extra SELECT keywords from each CASE branch—now the CASE directly evaluates and returns the calculated value.
• Simplified the WHEN condition to directly check getTotalPersons(num) IS NOT NULL (assuming it's a scalar function). If getTotalPersons returns a table instead, you'd need to adjust the condition to EXISTS (SELECT * FROM getTotalPersons(num)) to check if any rows are returned.

Quick Check for Edge Cases #

If getTotalPersons(num) might return a valid numeric value of 0 (not NULL), your logic will still work as intended. If you meant to handle cases where the function returns no rows instead of NULL, swap the condition to EXISTS (SELECT * FROM getTotalPersons(num)) as mentioned earlier.

内容的提问来源于stack exchange，提问作者enneenne