CODEWITHSUNDEEP
databasepostgresqltriggersplpgsql
ThreeFingerMark
ThreeFingerMark

Reputation: 989

Query has no destination for result data after trigger

I have a problem with my trigger. On inserting a new line it will check if the article has not sold. I can do it in the software but I think its better when the DB this does. 

-- Create function
CREATE OR REPLACE FUNCTION checkSold() RETURNS TRIGGER AS $checkSold$
    BEGIN
        SELECT offer_id FROM offer WHERE offer_id = NEW.offer_id AND date_sale IS NULL;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'The Offer is Sold!';
    END IF;
    RETURN NEW;
END;
$checkSold$ LANGUAGE plpgsql;


-- Create trigger
Drop TRIGGER checkSold ON tag_map;
CREATE TRIGGER checkSold BEFORE INSERT ON tag_map FOR EACH ROW EXECUTE PROCEDURE checkSold();

INSERT INTO tag_map (tag_id,offer_id) VALUES (824,80);

And this is the error after the insert.

[WARNING  ] INSERT INTO tag_map (tag_id,offer_id) VALUES (824,80)
            ERROR:  query has no destination for result data
            HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
            CONTEXT:  PL/pgSQL function "checksold" line 2 at SQL statement

What is wrong with the trigger? Without it works great.

Upvotes: 12

Views: 36476

Answers (1)

Milen A. Radev
Milen A. Radev

Reputation: 62593

Replace

SELECT offer_id FROM offer WHERE offer_id = NEW.offer_id AND date_sale IS NULL;

with

PERFORM offer_id FROM offer WHERE offer_id = NEW.offer_id AND date_sale IS NULL;

as suggested.

More info in the manual ("38.5.2. Executing a Command With No Result").

Upvotes: 24

Related Questions