Error while creating stored procedure through ODBC connection

["Gary Greenberg" <gary(at)icontrol(dot)com>]

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