By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,773 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,773 IT Pros & Developers. It's quick & easy.

Dazed and Confused

P: n/a
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: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
When grilled further on (Mon, 16 Feb 2004 07:39:25 -0500),
Terry Lee Tucker <te***@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAkAwvnoACgkQLQ/DKuwDYzkulQCgmKAb9O2v/Dqn68Egy7gm9TFe
DZwAnAwDTXgKzzsGtpJ6OIHZOmICIpD1
=xdm5
-----END PGP SIGNATURE-----

Nov 22 '05 #2

P: n/a
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 <te***@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: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #3

P: n/a
Robert Creager <Ro************@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.