[GENERAL] Seeking a better PL/pgSQL editor-debugger
Is a better PL/pgSQL editor / debugger than pgAdmin III or phpPgAdmin available anywhere? I ask because I was stuck for two days on the following error message: ERROR: syntax error at or near LOOP CONTEXT: compile of PL/pgSQL function gen_history near line 126 and neither of those tools offered any more help than that. It turned out the error was a missing ; way back in line 53, and it took two days of trial and error and staring at the code to find it. (On the positive side, pgAdmin IIIs use of different colors to distinguish different program elements [variables, keywords, string constants, comments] made the staring part easier to do.) Ive learned that pgAdmin syntax error can mean anything from a missing ; to a faulty block structure to an undeclared variable to I dont know what else, and as witness the example that error may be nowhere near the line that is flagged. So Im looking for a PL/pgSQL tool that would at least provide more diagnostic error messages. Preferably, it would also offer some sort of breakpoint function to let the developer see the values of variables at specified points in the code. I looked on the pgAdmin web site. The only place a PL/pgSQL debugger was mentioned was on the to do page, under major projects (http://www.pgadmin.org/development/todo.php), which I guess means dont hold your breath. As editors, the two pgAdmin tools apparently dont offer elementary functions such as find and replace, which means I have to slurp my code out into a text editor when I really need these things. So I guess my questions are: Is it the case that the pgAdmin tools actually do offer these features, but I just havent found them yet? If so, can you show me where they are? Are these functions available through add-ons to either pgAdmin tool? If so, where can I get these add-ons? Are there other PL/pgSQL editors that provide these functions? If so, what? Obviously, Id prefer a free one, but would pay for one if necessary. ~ TIA ~ Ken
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
Ken Winter [EMAIL PROTECTED] writes: I ask because I was stuck for two days on the following error message: ERROR: syntax error at or near LOOP CONTEXT: compile of PL/pgSQL function gen_history near line 126 and neither of those tools offered any more help than that. It turned out the error was a missing ; way back in line 53, and it took two days of trial and error and staring at the code to find it. What Postgres version are you using? I would blame the backend more than the client tools for the failure to localize this syntax error. We've made significant progress in 8.0 and again in 8.1 on improving plpgsql's error messages --- if you are not on 8.1 the first thing to try is a backend upgrade. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
On 28 Jan 2006 at 13:16, Ken Winter wrote: I've learned that pgAdmin syntax error can mean anything from a missing ; to a faulty block structure to an undeclared variable to. I AFAIK, the error messages you're seeing are generated by the database backend and not by pgAdmin - pgAdmin simply passes on to you what it receives from the backend. --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
There is a better editor available: http://www.amsoftwaredesign.com http://www.amsoftwaredesign.com/lightning_admin.php I invite you to try it out :-) It provides all the find/replace goto line number etc, many of the features of a IDE such as Delphi 2006 or VS, it also has super nice tabbed based MDI. We also offer code completion for schemas, user functions and built in functions. I will probably get flamed but PG Admin III's function editing is not much better than notepad. The server provides the error messages, and on versions before 8.0 they did not do much checking at all. Version 8.1 does the best checking and I advise you to upgrade your server, it's not that difficult. Another thing to keep in mind is if you restore a dump from 7.x to 8.x it will not do any checking unless you make a tweak, see this article: http://www.milwaukeesoft.com/forums/viewtopic.php?t=83 As far as I know this affects 8.0 and 8.1, I am sure someone will correct me if I am wrong :-) Hope this helps, -- Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql 8.x Ken Winter wrote: Is a better PL/pgSQL editor / debugger than pgAdmin III or phpPgAdmin available anywhere? As editors, the two pgAdmin tools apparently don’t offer elementary functions such as find and replace, which means I have to slurp my code out into a text editor when I really need these things. So I guess my questions are: * Is it the case that the pgAdmin tools actually do offer these features, but I just haven’t found them yet? If so, can you show me where they are? * Are these functions available through add-ons to either pgAdmin tool? If so, where can I get these add-ons? * Are there other PL/pgSQL editors that provide these functions? If so, what? Obviously, I’d prefer a free one, but would pay for one if necessary. ~ TIA ~ Ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
Tom ~ Thanks for the news. I'm on PostgreSQL 7.4.7, alas, and there's nothing I can do about it because it resides on a host that I don't control. I suppose if I don't find an alternative, I could move my development work to a local installation of PostgreSQL 8.1. ~ Ken -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, January 28, 2006 1:26 PM To: Ken Winter Cc: PostgreSQL pg-general List Subject: Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger Ken Winter [EMAIL PROTECTED] writes: I ask because I was stuck for two days on the following error message: ERROR: syntax error at or near LOOP CONTEXT: compile of PL/pgSQL function gen_history near line 126 and neither of those tools offered any more help than that. It turned out the error was a missing ; way back in line 53, and it took two days of trial and error and staring at the code to find it. What Postgres version are you using? I would blame the backend more than the client tools for the failure to localize this syntax error. We've made significant progress in 8.0 and again in 8.1 on improving plpgsql's error messages --- if you are not on 8.1 the first thing to try is a backend upgrade. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
Tony Caduto [EMAIL PROTECTED] writes: Another thing to keep in mind is if you restore a dump from 7.x to 8.x it will not do any checking unless you make a tweak, see this article: http://www.milwaukeesoft.com/forums/viewtopic.php?t=83 As far as I know this affects 8.0 and 8.1, I am sure someone will correct me if I am wrong :-) That is true in 8.0 but not anymore in 8.1. The reason we invented the pg_pltemplate catalog is to ensure that old dumps of PL language definitions will track desired changes without such manual heroics ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
PG Lightning does Code Completion. I don't think there is a frontend tool that can step through a PL/pgSQL function. Ben ""Ken Winter"" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Is a better PL/pgSQL editor / debugger than pgAdmin III or phpPgAdmin available anywhere? I ask because I was stuck for two days on the following error message: ERROR: syntax error at or near "LOOP" CONTEXT: compile of PL/pgSQL function "gen_history" near line 126 and neither of those tools offered any more help than that. It turned out the error was a missing ; way back in line 53, and it took two days of trial and error and staring at the code to find it. (On the positive side, pgAdmin IIIs use of different colors to distinguish different program elements [variables, keywords, string constants, comments] made the staring part easier to do.) Ive learned that pgAdmin syntax error can mean anything from a missing ; to a faulty block structure to an undeclared variable to I dont know what else, and as witness the example that error may be nowhere near the line that is flagged. So Im looking for a PL/pgSQL tool that would at least provide more diagnostic error messages. Preferably, it would also offer some sort of breakpoint function to let the developer see the values of variables at specified points in the code. I looked on the pgAdmin web site. The only place a PL/pgSQL debugger was mentioned was on the to do page, under major projects (http://www.pgadmin.org/development/todo.php), which I guess means dont hold your breath. As editors, the two pgAdmin tools apparently dont offer elementary functions such as find and replace, which means I have to slurp my code out into a text editor when I really need these things. So I guess my questions are: Is it the case that the pgAdmin tools actually do offer these features, but I just havent found them yet? If so, can you show me where they are? Are these functions available through add-ons to either pgAdmin tool? If so, where can I get these add-ons? Are there other PL/pgSQL editors that provide these functions? If so, what? Obviously, Id prefer a free one, but would pay for one if necessary. ~ TIA ~ Ken
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
That is true in 8.0 but not anymore in 8.1. The reason we invented the pg_pltemplate catalog is to ensure that old dumps of PL language definitions will track desired changes without such manual heroics ... Tom, Thanks for the info, I will update my article to reflect that. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
