CODEWITHSUNDEEP
databasepostgresqlpgadmin
Matteo Novelli
Matteo Novelli

Reputation: 13

Best way to refactor more functions in pgadmin

I'm new in PostgreSQL, and i was lookinng for a good way to find and replace a word (the name of a function) in multiple functions.

I'm using the latest version of PostgreSQL.

Does someone knows how can I do it in a good manner?

Thanks

Upvotes: 1

Views: 299

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51609

pgadmin offers "Find and Replace" window for such operations. Staying neutral about the approach (if pgadmin offers - then I can't say it's a bad practice) you can copy all functions definitions from pg_catalog and use this approach for "bulk" object rename inside function bodies. Or select with replace and then run the changed definitions.

Here's why I would recommend not doing this. Let's say you want to change length( to new_length( in all functions. if you just 

t=# select proname,replace(prosrc,'length(','new_length(') from pg_proc where prosrc like '%length(%' limit 3;
  proname   |                  replace
------------+--------------------------------------------
 bit_length | select pg_catalog.octet_new_length($1) * 8
 bit_length | select pg_catalog.octet_new_length($1) * 8
 bit_length | select pg_catalog.new_length($1)
(3 rows)

As you see you affect more names then just length. Of course you can use .length( to avoid octet_length and bit_length, but there is no guarantee you dont use just length in your code (without pg_catalog.). and so on and so forth. So if you blindly replace all occurences you risk to brake the code. And if you check them all, you do one by one anyway. So maybe just go through every occurrence and check every one before changing?..

Lastly I'd use psql for such work, or at least mix it with pgadmin. As psql has a set of meta commands, very useful. Eg function definition can be get by just sf fn_name. Running the definition puts it to history, that can be comfortably edited in vim or any other editor and rerun with changed names. (vim offers :%s/oldname/newname/g which is same Find and Replace and so on...)

Upvotes: 1

Related Questions