PostgreSql procedure return query has no destination for result data

Question

I am building a procedure, which is called by a trigger, after an insert, and an error is occurring when I use a select statement in its body.

When I do NOT use select, to get a value, there is NO error when the trigger calls this procedure:

BEGIN;

CREATE OR REPLACE FUNCTION calc_virtual()
RETURNS trigger
LANGUAGE plpgsql

AS $function$ 

DECLARE
    _virtual RECORD;
    _value_calc DECIMAL;
    _complet BOOLEAN;

BEGIN
    _value_calc = 0

    FOR _virtual IN SELECT value
                      FROM virtual
                     WHERE id = NEW.id 
    LOOP                             
        _value_calc = _value_calc-(value * 1.5);            
    END LOOP;

    INSERT INTO appointment (value) VALUES (_value_calc);

    RETURN NEW;
END;

$function$;

COMMIT;

When I use select, to get a value, an error occurs when the trigger calls this procedure:

BEGIN;

CREATE OR REPLACE FUNCTION calc_virtual()
RETURNS trigger
LANGUAGE plpgsql

AS $function$ 

DECLARE
    _virtual RECORD;
    _value_calc DECIMAL;
    _complet BOOLEAN;

BEGIN

    FOR _virtual IN SELECT value
                      FROM virtual
                     WHERE id = NEW.id 
    LOOP
        _value_calc = 0;
        _complet = TRUE;      

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

Thanks por any help!

As the error message says: You need to store the result of a SELECT statement somewhere — , Oct 09 '19 at 15:20

score 0 · Accepted Answer · answered Oct 09 '19 at 15:24

0

You have to use SELECT ... INTO in PL/pgSQL. No variable value_active will magically be created.

SELECT  value_active INTO _active
FROM    appointment_virtual
WHERE   name = _virtual.name;

IF FOUND THEN
     _value_calc = _value_calc-(_active * 1.5);
...

You have to declare _active in the DECLARE section.

answered Oct 09 '19 at 15:24

Laurenz Albe

209,280
17
206
263

Solved. Thank you Laurenz! – Antonio José Oct 09 '19 at 17:48

PostgreSql procedure return query has no destination for result data

1 Answers1