On 04/03/20, David G. Johnston (david.g.johnston@xxxxxxxxx) wrote: > On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston <david.g.johnston@xxxxxxxxx> > wrote: > > > On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange < > > rory@xxxxxxxxxxxxxxxxxx> wrote: > > > >> Any thoughts on how to wrap pl/pgsql function dropping and recreation code > >> within a wrapper pl/pgsql function? > > > > > > Not endorsing this but dynamic SQL works just fine (though can get hard to > > read). Use format() and EXECUTE ... USING liberally. > > > > > Or, more readable depending upon your trust level: > > INSERT INTO dynamic_codes VALUES (1, 'DROP FUNCTION ...'); > > CREATE FUNCTION execute_dynamic(code_id int) > AS $$ > sql_cmd := (SELECT val FROM dynamic_codes WHERE id = code_id); > EXECUTE sql_cmd; > $$; > > SELECT execute_dynamic(1); Thanks very much for the useful examples. Based on your second example, we could drop and then reload a upgrade schema with entries in dynamic_codes then use execute_dynamic(...) as you suggest. Any idea on how to run execute_dynamic across many databases at roughly the same time? I'm just wondering if Guyren Howe's idea of having many transactions open waiting for a clock time to commit is in fact feasible due to (presumably) having to have all the connections open to every database from the client until the transactions complete.
