Is there a trick to getting Postgres PL/pgSQL server side scripts to work?

Question

I've written triggers and other functions for Postgres which work fine on my development PC, but when I deploy them on the Webfaction server I get an

ERROR: ODBC: State 42703: ERROR: record "new" has no field "move";

It seems to see plpgsql's new keyword as a record instead of a plpgsql builtin word.

In initially thought I'd found the problem in the documentation at https://docs.webfaction.com/user-guide/databases.html#enabling-procedural-languages-for-postgresql which said plpgsql is not installed by default. But entering create language plpgsql; at the psql prompt returns

ERROR:  language "plpgsql" already exists

Any suggestions on how to get my pgsql scripts to run?

Robert

Answer 1

1

You would need to use a private database or ask us in a support ticket to import the sql. Triggers require super user access to enable.

permanent link

answered 18 Apr, 21:43

johns ♦♦
5.2k●2●12
accept rate: 23%

Many thanks for the prompt reply. I've found the documentation on installing a private database and will proceed with that.

(19 Apr, 06:06) roblaing

Answer 2

I sorted the problem by not using triggers (which in postgresql are functions without parameters and so have to use the NEW.columname, OLD.columname syntax which may have only been introduced after version 9.4).

By using "normal" plpgsql functions, passing the parameters to the function instead of using NEW.foo, OLD.foo etc, instead of triggers, the script works fine without using a private database instance.

Is there a trick to getting Postgres PL/pgSQL server side scripts to work?

Follow this question

Related questions