Re: [SQL] Function definitions - batch update
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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] on insert rule with default value
My rule below does not insert the the same uuid value into the test_log table as is created in the test table when I insert a new value. I know I've worked through this before, but I'm not remembering why this is. What's a right way to do this? create table test ( anid uuid not null default encode( gen_random_bytes( 16 ), 'hex' )::uuid primary key, value text ); create table test_log ( anid uuid, value text, op text, attime timestamp with time zone ); create rule test_rule_a as on insert to test do ( insert into test_log ( anid, value, op, attime ) values ( new.anid, new.value, 'insert', now() ) ); -- Ron Peterson Network & Systems Administrator Mount Holyoke College http://www.mtholyoke.edu/~rpeterso -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to split up phone numbers?
(anonymous) wrote: > is there a way to split up phone numbers? > I know that's a tricky topic and it depends on the national > phone number format. > I'm especially interested in a solution for Germany, Swizerland and Austria. > I've got everything in a phone number column that makes hardly sense like: > +49432156780 > 0049 4321 5678 0 > 04321/5678-0 > and so on... > Those 3 samples are actually the same number in different notations. > Aim would be to get a normalized number split up in 4 seperate columns > nr_nation > nr_city > nr_main > nr_individual > so I end up with > 49 4321 5678 0 for central > 49 4321 5678 42 for Mr. Smith > Is this doable? > It would be a start to at least split off nr_nation and nr_city. I seem to faintly remember that there are some anomalies in the German area codes, i. e. "+49 xy" may be city A, with "+49 xyz" being city B. Obviously, you will also not be able to differentiate between normal and extension numbers if they aren't properly marked up in the input. Tim -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function definitions - batch update
Thanks for the hints, I'll give it a try. pozdrowienia mk 2012/2/21 Tom Lane > 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 >
