Thread: edit function

Hi all,
On a PostGreSQL 8.1 (Debian Etch)

I have a function like this:

CREATE FUNCTION f_active_client(character varying)
        RETURNS character varying
     AS $_$
  DECLARE
   v_modem ALIAS FOR $1;
   v_firstuse BOOLEAN;
   v_admactive BOOLEAN;
   v_codeclt varchar;
BEGIN
    SELECT INTO (...)
         IF FOUND THEN
            (...)
    ELSE
       RETURN 'ERROR';
         END IF;
END;
$_$


I have to insert several IF statements in the "IF FOUND" one.
I wont do it one time, I will insert them one by one.

I cannot stop too much long the PG server (a /etc/init.d restart time
is OK, more is not)

Dumping and the restoring is not possible because of too long downtime.

DELETing CREATing the function is not possible because of dependency.

When looking  at the help, '\h' has no REPLACE FUNCTION.
Only ALTER FUNCTION

How to manage it?

Thank you.

--
       Architecte Informatique chez Blueline/Gulfsat:
    Administration Systeme, Recherche & Developpement
                                    +261 34 29 155 34

Le lundi 7 septembre 2009 à 08:26:27, Rakotomandimby Mihamina a écrit :
> [...]
> I have to insert several IF statements in the "IF FOUND" one.
> I wont do it one time, I will insert them one by one.
>
> I cannot stop too much long the PG server (a /etc/init.d restart time
> is OK, more is not)
>
> Dumping and the restoring is not possible because of too long downtime.
>
> DELETing CREATing the function is not possible because of dependency.
>
> When looking  at the help, '\h' has no REPLACE FUNCTION.
> Only ALTER FUNCTION
>
> How to manage it?
>

You're probably looking for CREATE OR REPLACE.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

On Sep 7, 2009, at 2:26 AM, Rakotomandimby Mihamina wrote:

> I have a function like this:
>
> CREATE FUNCTION f_active_client(character varying)
>       RETURNS character varying
>    AS $_$
> DECLARE
>  v_modem ALIAS FOR $1;
>  v_firstuse BOOLEAN;
>  v_admactive BOOLEAN;
>  v_codeclt varchar;
> BEGIN
>     SELECT INTO (...)
>        IF FOUND THEN
>           (...)
>     ELSE
>        RETURN 'ERROR';
>        END IF;
> END;
> $_$
>
>
> I have to insert several IF statements in the "IF FOUND" one.
> I wont do it one time, I will insert them one by one.
>
> I cannot stop too much long the PG server (a /etc/init.d restart
> time is OK, more is not)
>
> Dumping and the restoring is not possible because of too long
> downtime.
>
> DELETing CREATing the function is not possible because of dependency.
>
> When looking  at the help, '\h' has no REPLACE FUNCTION.
> Only ALTER FUNCTION
>
> How to manage it?


Write it exactly as you have above, but replace the first line with:

CREATE OR REPLACE FUNCTION f_active_client(character varying)





John DeSoi, Ph.D.