1

The following,

CREATE TRIGGER _trigger_insert_form_metadata
AFTER INSERT
ON my_table
FOR EACH ROW
WHEN _conditions_form_metadata(NEW)
EXECUTE PROCEDURE _trigger_insert_form_metadata_function();

It getting the error,

ERROR:  syntax error at or near "_conditions_form_metadata"
LINE 5: WHEN _conditions_form_metadata(NEW)

The function successfully creates,

CREATE OR REPLACE FUNCTION _conditions_form_metadata(doc jsonb)
RETURNS boolean AS
$BODY$
BEGIN
   RETURN (doc ->> 'type') = 'data_record'
      AND (doc #>> '{contact,_id}') IS NOT NULL
      AND (doc ->> 'form') IS NOT NULL;
END
$BODY$ LANGUAGE plpgsql;

I want to call the function for the WHEN conditions because I need several triggers with identical conditions.

What it wrong?

2

Your function is defined to accept a jsonb parameter not a record of type my_table. Additionally, the condition after a when has to be enclosed in parentheses.

Assuming my_table has a jsonb column called doc you need to change the call to:

when (_conditions_form_metadata(new.doc))

Alternatively you need to change _conditions_form_metadata to accept a record of type my_table

CREATE OR REPLACE FUNCTION _conditions_form_metadata(p_row my_table)

Then access the JSONB column using (p_row.doc ->> 'type')

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.