Postgresql - Edit function signature

Question

POSTGRESQL 8.4.3 - i created a function with this signature

CREATE OR REPLACE FUNCTION logcountforlasthour() RETURNS SETOF record AS

realised i wanted to change it to this

CREATE OR REPLACE FUNCTION logcountforlasthour() RETURNS TABLE(ip bigint, count bigint) record AS

but when i apply that change in the query tool it isnt accepted or rather it is accepted, there is no syntax error, but the text of the function has not been changed. even if i run "DROP FUNCTION logcountforlasthour()" between edits the old syntax comes back

if i edit the body of the function, thats fine, it changes but not the signature

is there something i'm missing

thanks

Answer 1

From the PostgreSQL 8.4 manual:

To replace the current definition of an existing function, use CREATE OR REPLACE FUNCTION. It is not possible to change the name or argument types of a function this way (if you tried, you would actually be creating a new, distinct function). Also, CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function. (When using OUT parameters, that means you cannot change the names or types of any OUT parameters except by dropping the function.)

If you drop and then recreate a function, the new function is not the same entity as the old; you will have to drop existing rules, views, triggers, etc. that refer to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function. Also, ALTER FUNCTION can be used to change most of the auxiliary properties of an existing function.

The user that creates the function becomes the owner of the function.

and also note:

... PostgreSQL allows function overloading; that is, the same name can be used for several different functions so long as they have distinct argument types.