473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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: te***@esc1.com

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

Nov 22 '05 #1
3 1691
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Haoyu Zhang | last post by:
Dear Friends, Python assignment is a reference assignment. However, I really can't explain the difference in the following example. When the object is a list, the assignment seems to be a...
2
by: Brian Roberts | last post by:
I'm confused about the use of hasattr/getattr, or possibly namespaces. I know how to do this: class UnderstandThis(object): def do_foo(self): pass def do_bar(self): pass def doit(self, cmd):...
11
by: Ohaya | last post by:
Hi, I'm trying to understand a situation where ASP seems to be "blocking" of "queuing" requests. This is on a Win2K Advanced Server, with IIS5. I've seen some posts (e.g.,...
6
by: ree32 | last post by:
I am a bit confused with capabilities of XML. I have an XML document with information on images(photos). Is there way to use XSL/XSLT to create a page that will display the images as gallery. ...
5
by: Jeff Amiel | last post by:
Yes, I've read the FAQ's... I'm still confused. I'm trying to help out a buddy to extract data from an .mdb file that has special 'permissions' on it. If I try to open it with the standard...
10
by: Lauren Wilson | last post by:
Ok I have searched the MS website for info on this. I am totally confused. If I want to deploy an Access 2003 app and allow my users to run it using Access 2003 Runtime, where do I get the...
1
by: Benny Ng | last post by:
Hi,All, Export Method: ------------------------------------------------------------------------- strFileNameExport = "Results" Response.Clear() Response.Buffer = True...
2
by: Daniel | last post by:
I'm new to .Net and all of its abilities so I hope this makes sense. Basically I'm confused on when is the appropriate time to use web forms controls vs. regular HTML. For example in ASP...
11
by: timmu | last post by:
Someone asked me a question about integer division and printf yesterday, I tell him he should do a casting to float/double before you do any interger division. But he doesn't think so, so I try...
2
by: Peter | last post by:
Hi, I have a problem with Listview using checkboxes. If i check items by code BEFORE the form is shown the Listview.Items are confused during the ItemChecked Event !!! After showing the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.