I am trying to generate a database by running DDL script through the ODBC connection.
It went through almost to the end and then barfed on creating procedure.
Here is a snippet from the generation log:
*** Executing statement 311:
CREATE FUNCTION mask_new_permission () RETURNS trigger AS $mask_new_permission$
begin
new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK) from permissions, 1)
*** Execution of statement failed:
ERROR: unterminated dollar-quoted string at or near "$mask_new_permission$
begin
new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK) from permissions, 1)";
Error while executing the query
SQLSTATE = 42601
*** Executing statement 312:
return new
*** Execution of statement failed:
ERROR: syntax error at or near "return";
Error while executing the query
SQLSTATE = 42601
*** Executing statement 313:
end
*** Statement successfully executed.
*** Executing statement 314:
$mask_new_permission$ LANGUAGE plpgsql
*** Execution of statement failed:
ERROR: unterminated dollar-quoted string at or near "$mask_new_permission$ LANGUAGE plpgsql";
Error while executing the query
SQLSTATE = 42601
*** Executing statement 315:
create trigger permissions_bit before insert on permissions
for each row execute procedure mask_new_permission()
*** Execution of statement failed:
ERROR: function mask_new_permission() does not exist;
Error while executing the query
SQLSTATE = 42883
When I am executing the same piece of code from psql prompt it is going through without complaining:
ops2=> CREATE FUNCTION mask_new_permission () RETURNS trigger AS $mask_new_permission$
ops2$> begin
ops2$> new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK) from permissions, 1);
ops2$> return new;
ops2$> end;
ops2$> $mask_new_permission$ LANGUAGE plpgsql;
CREATE FUNCTION
ops2=>
ops2=>
ops2=> create trigger permissions_bit before insert on permissions
ops2-> for each row execute procedure mask_new_permission();
CREATE TRIGGER
Can anyone explain it to me, please?
Thx,
Gary
BTW, ODBC driver is PSQLODBC30A.dll