Thread: Dazed and Confused

I have just upgraded Postgres from version:
PostgreSQL 7.2.3-RH on i686-pc-linux-gnu, compiled by GCC 2.96

to:
PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)

All of my trigger code loaded successfully; however, I am now trying to make a
change in some of the trigger code and cannot. When I try to reload the code
as in, \i cust/cust_preupd_func.plsql, I get an error. The error is:
psql:logs/logs_preupd_func.plsql:132: ERROR:  ProcedureCreate: cannot change
return type of existing function.
        Use DROP FUNCTION first.
I didn't change the return type at all. In fact, if I try and reload any
trigger code by the method above, I get the same error even though I didn't
change a line of code.

All of my trigger functions return OPAQUE. I notice from some of the examples
in the docs, i.e., Example 19-1. A PL/pgSQL Trigger Procedure Example, that
the return type is TRIGGER.

Am I going to have to change all of my trigger code?

Thanks for any help you can give...
--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

When grilled further on (Mon, 16 Feb 2004 07:39:25 -0500),
Terry Lee Tucker <terry@esc1.com> confessed:

> as in, \i cust/cust_preupd_func.plsql, I get an error. The error is:
> psql:logs/logs_preupd_func.plsql:132: ERROR:  ProcedureCreate: cannot change
> return type of existing function.
>         Use DROP FUNCTION first.

Per the error message, execute DROP FUNCTION function_name( ) before trying to
CREATE FUNCTION.

I believe OPAQUE is deprecated, and the return type of TRIGGER should be used.
But, I still have a trigger which I have not updated that is OPAQUE, and it
loads just find after emitting:

psql:dbTriggers.sql:33: WARNING:  changing return type of function
observations_trigger from "opaque" to "trigger"

Cheers,
Rob

--
 05:53:40 up 1 day, 13:36,  2 users,  load average: 0.07, 0.17, 0.33
Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003

Hi Robert,

I changed the return type from OPAQUE to TRIGGER and the trigger function
reloaded without any complaint. I suppoe that when I loaded this new version
(with the output from pg_dumpall) that the return type was automatically set
to TRIGGER? I couldn't understand the fact that the code initally loaded
without complaint, but now, upon trying to reload, I was getting an error
with the same code.

Anyway, thanks for the response...

On Monday 16 February 2004 07:58 am, Robert Creager saith:
> When grilled further on (Mon, 16 Feb 2004 07:39:25 -0500),
>
> Terry Lee Tucker <terry@esc1.com> confessed:
> > as in, \i cust/cust_preupd_func.plsql, I get an error. The error is:
> > psql:logs/logs_preupd_func.plsql:132: ERROR:  ProcedureCreate: cannot
> > change return type of existing function.
> >         Use DROP FUNCTION first.
>
> Per the error message, execute DROP FUNCTION function_name( ) before trying
> to CREATE FUNCTION.
>
> I believe OPAQUE is deprecated, and the return type of TRIGGER should be
> used. But, I still have a trigger which I have not updated that is OPAQUE,
> and it loads just find after emitting:
>
> psql:dbTriggers.sql:33: WARNING:  changing return type of function
> observations_trigger from "opaque" to "trigger"
>
> Cheers,
> Rob

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> I believe OPAQUE is deprecated, and the return type of TRIGGER should be used.

Correct.

> But, I still have a trigger which I have not updated that is OPAQUE, and it
> loads just find after emitting:

> psql:dbTriggers.sql:33: WARNING:  changing return type of function
> observations_trigger from "opaque" to "trigger"

Right, there is a narrow hack in CREATE TRIGGER that does that to allow
loading of existing dump scripts.  But I think what Terry is trying to
do is modify an existing trigger function with

    CREATE OR REPLACE trigfunc() RETURNS OPAQUE AS ...

and the system won't (and shouldn't) let him change the function return
type back to OPAQUE.

The only answer is to replace OPAQUE by TRIGGER in your function
scripts.

            regards, tom lane