CODEWITHSUNDEEP
postgresqlfunctionstored-procedurestriggersinsert
G Stick
G Stick

Reputation: 49

Trigger prevents insertion of data into table

I have the following problem: I'm trying to insert data into a table, however my Trigger is preventing me from performing the insert. My trigger is to insert the codigolle into a log table when a given 'year' <'2000' is entered, but I'm encountering the following error:

" 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 funcaotriggerxx () line 5 at SQL statement "

TRIGGER

CREATE FUNCTION funcaoTriggerXX() RETURNS trigger AS $valor$
DECLARE
    cod INT;
BEGIN
    SELECT codigolivro 
    FROM edicao
    WHERE ano < '2000';

    cod = codigolivro;

    INSERT INTO log (codigolivro) VALUES (cod);

    RETURN cod;
END;
$valor$ LANGUAGE plpgsql;

CREATE TRIGGER codigoLivros AFTER INSERT ON edicao
EXECUTE PROCEDURE funcaoTriggerXX();

PROCEDURE

CREATE OR REPLACE FUNCTION seculoxx()
RETURNS integer AS $total2$
declare
    total2 integer;
BEGIN
   SELECT count(*) into total2 
   FROM edicao
   WHERE ano < 2000;
   RETURN total2;
END;
$total2$ LANGUAGE plpgsql;

SELECT

SELECT seculoxx();

INSERT

INSERT INTO edicao (codigolivro, numero, ano) VALUES ('1325','4','1990');

The structure of the table is also simple: edicao

log

Can someone help me?

Upvotes: 0

Views: 175

Answers (2)

Dunes
Dunes

Reputation: 40703

Look at how your select queries in funcaoTriggerXX and seculoxx differ. When you want to store a value from from a select query you need to SELECT column_name INTO variable_name. When you don't want to save the result then you need use PERFORM. eg. PERFORM SELECT ....

There's also a discrepancy between your language and what code will do. That is you say:

My trigger is to insert the codigolle into a log table when a given 'year' <'2000' is entered

I presume you mean the codigolivro for the row that was just inserted. However, as it stands. This trigger will essentially insert a random row with year < 2000 if any row in the edicao table meets that condition. Instead, you can use the NEW special variable to get the row that was just inserted, and you can use the WHEN clause of CREATE TRIGGER to control when your trigger fires.

Since you only care about the row that has just been inserted (not all rows in the entire table), then you can use the NEW special variable.

CREATE FUNCTION funcaoTriggerXX() RETURNS trigger AS $valor$
BEGIN
    INSERT INTO log (codigolivro) VALUES (NEW.codigolivro);
    RETURN NEW;
END;
$valor$ LANGUAGE plpgsql;

CREATE TRIGGER codigoLivros AFTER INSERT ON edicao
    FOR EACH ROW
    WHEN (NEW.ano < 2000)
    EXECUTE PROCEDURE funcaoTriggerXX();

Upvotes: 0

404
404

Reputation: 8562

Firstly your trigger function says it returns trigger (NULL/OLD/NEW) when it actually returns an INTEGER. Then as the error says, you can't do a SELECT in a plpgsql function without a destination. In other words, you need to assign the result of your SELECT statement to a variable.

SELECT codigolivro 
    INTO cod
FROM edicao
WHERE ano < '2000';

Your seculoxx() function is fine but doesn't need the intermediate variable:

RETURN count(*)
FROM edicao
WHERE ano < 2000;

Upvotes: 2

Related Questions