can't find procedure without a parameter on Postgres

From: Bryan Lewis (brya..aine.rr.com)
Date: Tue Feb 06 2007 - 06:35:21 EST

  • Next message: Tore Halset: "[OT] feathercast"

    I had this code working on an Oracle8 database:

            ProcedureQuery procQuery = new
    ProcedureQuery("proc_eu_update_begin");
            dc.performQuery(procQuery);

    The procedure is only a few sql statements to create a temporary table
    and takes no parameters. When I ported to Postgres 8.2, Cayenne says it
    can't find the procedure. The log shows:

        {call proc_eu_update_begin}
        org.postgresql.util.PSQLException: ERROR: relation
    "proc_eu_update_begin" does not exist

    I could execute the procedure manually with "select
    proc_eu_update_begin()". Note that the empty parentheses were required.

    I had other working procedures so I suspected it was the absence of
    parameters causing the problem. Adding a dummy parameter made things work:

        {call proc_eu_update_begin(?)} [bind: 'xx']

    So I have a work-around, but... maybe Cayenne could generate a call that
    Postgres would handle better, something like the empty parentheses in
    the manual command.

    P.S. The definition of the procedure (now a function in Postgres) looks
    like:

        CREATE OR REPLACE function proc_eu_update_begin()
          RETURNS void AS ...

    This archive was generated by hypermail 2.0.0 : Tue Feb 06 2007 - 06:36:00 EST