Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 97682116A138 for ; Tue, 21 Feb 2012 11:15:53 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RzrRV-000619-Nk for pgsql-sql@postgresql.org; Tue, 21 Feb 2012 15:15:52 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q1LFFYF8014805; Tue, 21 Feb 2012 10:15:34 -0500 (EST) To: Marcin Krawczyk cc: pgsql-sql@postgresql.org Subject: Re: Function definitions - batch update In-reply-to: References: Comments: In-reply-to Marcin Krawczyk message dated "Tue, 21 Feb 2012 08:28:10 +0100" Date: Tue, 21 Feb 2012 10:15:34 -0500 Message-ID: <14804.1329837334@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/64 X-Sequence-Number: 36357 Marcin Krawczyk writes: > I've come across a situation when I need to add some constant code to all > functions in my database. Does anyone know a way to batch update all > definitions ? I've got like 500 functions so doing it one by one will be > time consuming. If you're feeling like a DBA cowboy, become superuser and issue a direct UPDATE against the prosrc column of pg_proc, being careful not to update rows that aren't the functions you want to hit. Slightly saner would be to read pg_proc and construct CREATE OR REPLACE FUNCTION commands that you then EXECUTE. The latter, if not done as superuser, would at least ensure you didn't accidentally break any functions you don't own. In either case, I'd practice against a test copy of the database before doing this live ... regards, tom lane