How to edit PostgreSQL stored procedure?

postgresql stored-procedures stored-functions

27,225

Solution 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.

Solution 2

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.

Solution 3

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!

Solution 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.

Solution 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

View more solutions

27,225

Paul

It is just a website. Life is somewhere else. Answers to downvoted question won't be accepted. Period. My wishlist The right syntax of with in Delphi: with r := obj.MyRecord do begin r.Field1 := 1; r.Field2 := '2'; // ... end; Favorites https://stackoverflow.blog/2019/10/17/imho-the-mythical-fullstack-engineer/ http://www.catb.org/esr/faqs/smart-questions.html Stackoverflow How to reduce image size on Stack Overflow Unicode Is there a list of characters that look similar to English letters? Windows How to programmatically get DLL dependencies Device misdetected as serial mouse Catch MSVCR120 missing error message in Delphi Get members of COM object via Delphi Olevariant type Controlling the master speaker volume in Windows 7 Filename timestamp in Windows CMD batch script getting truncated https://www.joachim-bauch.de/tutorials/loading-a-dll-from-memory Linux How to control backlight by terminal command MS Visual Studio How to change "Visual Studio 2017" folder location? Delphi Convert a null-delimited PWideChar to list of strings Out-of-the-box flat borderless button TFDConnection.OnRecover is never fired when PostgreSQL stops TScrollBox with customized flat border color and width? How to redirect mouse events to another control? Creating object instance based on unconstrained generic type how to create a TCustomControl that behaves like Tpanel? https://stackoverflow.com/a/43990453 - How to hack into a read-only direct getter property With DDevExtensions you can disable storing Explicit... properties in the dfm https://github.com/RomanYankovsky/DelphiAST C Arrow operator (->) usage in C The Definitive C++ Book Guide and List GDI+ How to rotate Text in GDI+? GDI+ performance tricks MSXML schema validation with msxml in delphi How can I get English error messages when loading XML using MSXML Inno Setup: Save XML document with indentation PostgreSQL Checking for existence of index in PostgreSQL Web Will the IE9 WebBrowser Control Support all of IE9's features, including SVG? http://howtocenterincss.com MVC for Web, MVP for Winforms and MVVM for WPF. Just an observation: Most of the claims such as "I can't understand what you wrote", "Please provide more details", etc. are made by people with a relatively low SO score. People with the highest score on SO understand everything. Template: This is an abandoned question. Author has no longer anything to do with the topic and can neither approve nor decline your answer.

Updated on July 09, 2022

Comments

Paul almost 2 years

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.
- a_horse_with_no_name about 12 years
  
  Storing your stored procedure in an external file is highly recommended anyway (ideally in a version control system).
- Erwin Brandstetter about 12 years
  
  @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.
- a_horse_with_no_name about 12 years
  
  @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.
- Erwin Brandstetter about 12 years
  
  @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.
- Pavel Stehule about 12 years
  
  @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
ChristophK over 7 years

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".
10gistic over 7 years

@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.
Oleksii Kyslytsyn about 6 years

\ef works, but it won't save the function by editing sql in VIM programmers text editor.
bishop over 5 years

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