When trying to write a WorkflowTrigger with a plpgsql block statement, the following thing happens:
If not using dollar quotes, an error is thrown at the first line (usually the declaration): ERROR: syntax error at or near "INSERT".
The statement that is sent to the database is the following:
DECLARE _variable INTEGER ;
BEGIN
_variable := nextval('table_seq') ;
INSERT INTO TEST_TABLE (FIELD_1,FIELD_2,FIELD_3)
VALUES(_variable ,(('NAMETEST')) ,(('DESCRIPTIONTEST')));
END;
If using dollar quotes (as I should be doing as I try to execute a plpgsql block), 'ERROR: 42703: column "form_name" does not exist' shows up.
That is because the following statement is sent:
DO $$
DECLARE _variable INTEGER ;
BEGIN
_variable := nextval('table_seq') ;
INSERT INTO TEST_TABLE (FIELD_1,FIELD_2,FIELD_3)
VALUES(_variable ,@Form_NAME ,@Form_DESCRIPTION);
END $$;
The {FORM.NAME} variables are not mapped accordingly.
In conclusion:
without using dollar quotes the form variables are mapped accordingly, but the statement is not valid plpgsql code.
using dollar quotes the statement is valid plpgsql code, but the varialbes are not mapped accordingly.
I am using Postgresql 9.6 with Postgis 2.3.2 on Ubuntu Server 16.04 Xenial.
The version of GMSC is 16 EP 2
Does anyone have any idea on how I should write a WorkflowTrigger with a plplgsql block on a postgres workflow?
unfortunately it is the case because the system is not aware you are defining a PLSQL and, in the case of Postgres, using $ is a way to define string constant.
The custom trigger will not be an optimal solution for us, as we have hundreds of triggers.
Instead, for pgplsql we will use database functions with parameters that we will execute in the standard SqlTrigger (executing a function will not require dollar quotes and it works).
This will give us flexibility in adjusting parameters without having to recompile a plugin each time we have to change something.
Still, it would have been nice to have the same behaivour in all three of he supported databases (now executing blocks inside Oracle and MSSQL works fine, while in postgresql it does not and the block has to be set inside a database function).
