How to edit PostgreSQL stored procedure?

Question

I'm slowly moving from MSSQL to PostgreSQL.

In MSSQL I could call editing of already saved procedure or function, and the administration shell (SQL Server Management Studio) showed me procedure's text, so I did not have to store its source code somewhere in text file.

How to do the same with PostgreSQL the convenient way? I'm using pgAdmin III.

Alack

Storing your stored procedure in an external file is highly recommended anyway (ideally in a version control system). – Braddock

@a_horse_with_no_name: I prefer a different approach. I have a test db cluster for every productive db cluster (infrequently copied) where I experiment. When considered good, I implement in the productive db cluster. In addition to the usual backups I run frequent schema-only backups, especially before/after changes to the schema. Traditional repositories are of limited use for database schemas as the data changes constantly and many changes cannot (easily) be reverted. – Lollar

@ErwinBrandstetter: the problem with that approach is that you lose the overview which changes you need to apply to get a database from version x to version x+1. You need a centralized place where each change can be tracked (and ideally mapped e.g. to an issue ticket). If you have more than one environment (development, test, staging, validation, regression, pre-production, production) and maybe even more than one version in production (think different countries) I don't see how you can keep track of all changes without a VCS. – Braddock

@a_horse_with_no_name: Of course, my approach has its limitations. If the environment gets more complex, like you describe, a VCS may be in order. It should be a good solution for most users, though. – Lollar

@Ervin - using external files has significantly important advantages: VCS, possible using preferred editors, better possibility to organise and comment code. But using your system for deploying is good idea and it is not in collision to using files proposal – Continental

Answer 1

There're 2 clients included in the official distributions of Postgres - the CLI one psql and a GUI one pgAdmin. Both support what you want: for psql it's \ef and for pgAdmin - right-click on function, "Properties", "Code" tab.

Murphey

@redolent, you may need to manually add ; at the end of the opened file, or just add a single ; and hit enter if you've already closed the editor. – Stollings

\ef works, but it won't save the function by editing sql in VIM programmers text editor. – Inhaul

Confirmed that adding a single ; after closing vim works as expected. So: \ef function; make changes; ZZ/wq/etc out of vim; ; on psql cli – Rivalry

Answer 2

In pgAdmin you can make your life easier if you activate this option:

File -> Options.. -> Query Tool -> [x] Copy SQL from main form to SQL dialogue

Then, whatever is displayed in the SQL pane will be copied to a newly opened Query Tool window. So, select the function in the object browser and click the magnifying glass icon in the tool bar.

Be aware of an open bug in the current version 1.14.2. By default, public has the EXECUTE privilege on functions. You can REVOKE this privilege - which is only useful for SECURITY DEFINER functions. But this REVOKE is missing in the reverse engineered DDL statements from pgAdmin (a NULL got confused with an empty ACL). Careful if you delete and recreate such a function!

Lollar

This makes a huge difference – Micronesia

Answer 3

It's also a convenient way to edit the code and test it.

1) Extract the code of a required SQL function from pgAdmin.

2) Place the code with the function into file.sql.

3) Create a shell/bat file in the same directory with file.sql:

psql -U postgres dbname < file.sql

4) Place a shortcut for the shell/bat file into a fast panel.

5) Edit the file with your favourite text editor and push the shortcut to update the function.

Cabezon

In general I find your contribution helpful, but in this specific case your suggestion is exactly what paul did NOT want to do: "so I did not have to store its source code somewhere in text file" - probably because he thinks that this approach is too complicated to be called "convenient". – Decennary

Answer 4

phpPgAdmin will let you edit your stored procedures and edit them within the interface. The comment left under your question about storing them externally for version control is highly recommended as well.

Prude

Answer 5

right click on the function in object tree (on the left side) -> Scripts -> Script CREATE

-or-

Execute new SQL query -> copy code of "create or replace function ..." to it

Then edit the script and do not forgot to execute it

Tantalizing