PostgreSQL触发器函数报错：查询结果无接收目标

Hey there, let's get this sorted out together! I can spot exactly what's tripping up your function.

The Root Cause #

That error message "PostgreSQL procedure return query has no destination for result data" happens when you run a SELECT statement in PL/pgSQL but don't specify where to store the result it returns.

Looking at your problematic code, this line is the issue:

SELECT value_active FROM appointment_virtual WHERE name = _virtual.name;

This query pulls a value_active value, but you haven't assigned it to any variable. PostgreSQL has nowhere to send that result, so it throws an error.

The Fix #

You need to use the SELECT ... INTO ... syntax to capture the query's output into a variable. Here's how to adjust your function properly:

1. Add a new variable in the DECLARE section to hold the value_active result.
2. Modify the SELECT statement to assign its output to this new variable.
3. Use the variable in your calculation logic instead of trying to reference value_active directly.

Corrected Full Code #

BEGIN;
CREATE OR REPLACE FUNCTION calc_virtual() RETURNS trigger LANGUAGE plpgsql AS $function$
DECLARE
    _virtual RECORD;
    _value_calc DECIMAL;
    _complet BOOLEAN;
    _value_active DECIMAL; -- New variable to store the SELECT result
BEGIN
    _value_calc = 0;
    _complet = TRUE; -- Initialize here to avoid uninitialized variable warnings
    -- Make sure to select 'name' from virtual since we reference _virtual.name later
    FOR _virtual IN SELECT value, name FROM virtual WHERE id = NEW.id LOOP
        SELECT value_active INTO _value_active 
        FROM appointment_virtual 
        WHERE name = _virtual.name;
        
        IF FOUND THEN
            _value_calc = _value_calc - (_value_active * 1.5);
        ELSE
            _complet = False;
        END IF;
    END LOOP;
    
    IF _complet THEN
        INSERT INTO appointment (value) VALUES (_value_calc);
    END IF;
    
    RETURN NEW;
END;
$function$;
COMMIT;

Quick Extra Tips #

• I moved the _complet = TRUE initialization to the start of the BEGIN block to avoid potential "uninitialized variable" errors later.
• I updated the initial SELECT from virtual to include the name column—if you don't select it, _virtual.name will be NULL, and your inner query will never find a matching row.

内容的提问来源于stack exchange，提问作者Antonio José