From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marcin Krawczyk <jankes.mk@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Function definitions - batch update
Date: Tue, 21 Feb 2012 10:15:34 -0500
Message-ID: <14804.1329837334@sss.pgh.pa.us> (raw)
In-Reply-To: <CABnqL30S6ZRBzC6DSTkh4d9pFoVXQpZzPjf9sLBQqCjsOTHiyg@mail.gmail.com>
References: <CABnqL30S6ZRBzC6DSTkh4d9pFoVXQpZzPjf9sLBQqCjsOTHiyg@mail.gmail.com>
Marcin Krawczyk <jankes.mk@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jankes.mk@gmail.com
Subject: Re: Function definitions - batch update
In-Reply-To: <14804.1329837334@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox