Re: drop function all - ?

You don't keep the SQL for your functions in
source code control?

When working on a database design, always keep your
original *and* modified SQL scripts so that you can
always create an empty version of your production
system. Part of that is creating drop scripts
for each object, including functions, that you create.
CYA.

For those of you playing fast and loose,
this query will produce a list of functions and arguments
in the schema public. Use it in a shell script loop
to drop your functions.

SELECT
n.nspname || '.' || p.proname || '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')'
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND n.nspname ~ '^public$';

DO NOT under ANY circumstances drop
ANY functions in the schema pg_catalog.

elein(at)varlena(dot)com

On Sun, Jul 13, 2003 at 02:02:15PM +0200, Együd Csaba wrote:
> Hi,
> is there any way to get rid of all of my functions in a database. I mean
> such an expression: DROP FUNCTION ALL;
>
> The point of this issue is that time to time one writes and rewrites
> functions with the same name and forgets to remove the existing ones. At a
> certain time one tries to call a function but don't know why it fails. It
> fails bacause there is an other (or more) function with the same name and
> with a very similar parameter list (the same number and convertable types).
>
> I'm in this situation....
> So I'd like to remove all my functions and recreate the latest ones from a
> file. Without dropping the whole database of corse.
>
> Any suggestions????
>
> Thank you,
>
> -- Csaba
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com)
> Version: 6.0.495 / Virus Database: 294 - Release Date: 2003. 06. 30.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>