postgresqlembedded-sqlecpg

Multiply two host variables in embedded SQL for PostgreSQL

I have problem multiplying two host variables in embedded SQL for PostgreSQL. The SQL-query is big but I have cut out the part that doesn't work.

Declaration:

EXEC SQL BEGIN DECLARE SECTION;
int var1;
int var2;
EXEC SQL END DECLARE SECTION;

Code:

CASE WHEN product_id = :var1 THEN :var1 * :var2
                             ELSE 0 END

The compilation works but I get the following error message at execution:

Errcode: -400

Errmsg: operator is not unique: unknown * unknown on line 1394

If I change the code to

CASE WHEN product_id = :var1 THEN 1 * :var2
                             ELSE 0 END

or

CASE WHEN product_id = :var1 THEN product_id * :var2
                             ELSE 0 END

or

CASE WHEN product_id = :var1 THEN :var1 * (1 * :var2)
                             ELSE 0 END

it works.

Is it possible to multiply two host variables? If not, is there any workaround? The last code example above works but I would like a solution that is not as ugly.

Solution

  • Try casting to integer:

    CASE WHEN product_id = :var1 THEN :var1::integer * :var2::integer
                         ELSE 0 END

    The error

    operator is not unique...

    can mostly be fixed by casting to the expected type