1
CREATE OR REPLACE FUNCTION calculate(nr char) RETURNS char AS $$
    BEGIN

      RETURN (SELECT COUNT(*) FROM patient_notitie WHERE pn_patient_nr = nr);

    END;
$$ LANGUAGE plpgsql;

This worked:

SELECT * FROM calculate('100001');

This is where it went wrong, when we try to create the trigger to call the function:

CREATE TRIGGER trigger_calculate AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate(pn_patient_nr);

It says the function doesn't exist:

ERROR: function calculate() does not exist SQL state: 42883

  • What exactly should the trigger do? Your function only returns a single value what would be the sense in that as a trigger? (Unrelated, but: it should be defined as returns integer not returns char because it returns a number not a string) – a_horse_with_no_name Nov 9 '16 at 17:20
2

Before creating trigger, you need to create a trigger function, which has to be (quoting the manual):

A user-supplied function that is declared as taking no arguments and returning type trigger

Like:

CREATE OR REPLACE FUNCTION calculate_t_f()
  RETURNS trigger AS
$$
BEGIN
...
-- do something
-- maybe with with calculate(char)...
...
END
$$ LANGUAGE plpgsql;

Then you can create your trigger with that trigger function (not your original function):

CREATE TRIGGER trigger_calculate
AFTER INSERT ON patient_notitie
EXECUTE PROCEDURE calculate_t_f();
1

Another possibility is that your two objects are created in different schemas:

If the function is created in SCHEMA1.CALCULATE(char) but the table is created in SCHEMA2.TABLE1 .. the trigger will execute from SCHEMA2, which will not have visibility into SCHEMA1 unless you GRANT it.

Also, leapfrogging off of a_horse_with_no_name's comment .. it sounds like you don't simply want to execute the function, but actually DO SOMETHING with the result... If that's the case, you'd want to actually write a full fledged PLSQL code block for your trigger.

DECLARE
  my_nr_count INTEGER;
BEGIN
  SELECT count(:NEW.nr) INTO my_nr_count FROM DUAL;
  :NEW.nr_count := my_nr_count;
  RETURN :NEW;
END;

Or something along those lines..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.