Thread: Executing dynamic procedure call

I am using a PL/pgSQL procedure. I am trying to hold the procedure
name in a table and then based on certain selection criteria get the
procedure name out of the table and execute it. I would like to pass a
row record, currently NEW, and retrieve the same rowtype. The
following code will compile but yields the result below it for either
of the two statements listed. Note that the procedure is triggered
from an insert running from a Perl script.

EXECUTE '"RuleRec.rule_procedure_name"(NEW)' INTO NEW;
 or
NEW := "RuleRec.rule_procedure_name"(NEW);

Result:

DBD::Pg:st execute failed: ERROR: operator does not exist: money <>
integer
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
CONTEXT: SQL statement "SELECT (( $1 - $2 - $3 - $4 - $%) <> 0)"

Can you help me code this correctly and, if possible, explain what's
happening here?

PG Ver: 8.1.3
DBD Ver: 1.48

Thanks.

Robert Young

tekwiz wrote:

> Result:
>
> DBD::Pg:st execute failed: ERROR: operator does not exist: money <>
> integer
> HINT: No operator matches the given name and argument type(s). You may
> need to add explicit type casts.
> CONTEXT: SQL statement "SELECT (( $1 - $2 - $3 - $4 - $%) <> 0)"

craig=# SELECT '0'::money <> 0;
ERROR:  operator does not exist: money <> integer
LINE 1: SELECT '0'::money <> 0;
                           ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.


You are trying to compare a value of `money' data type with a value of
`integer' data type. This is intentionally not permitted. Since, in your
case, it looks like the 0 is an integer literal you should be doing:

( moneyvar1 - moneyvar2 - moneyvar3etc ) <> '0'::money;

In the longer run you might want to look into using the NUMERIC data
type instead of the MONEY type.

--
Craig Ringer