Thread: Debugging question ...
Hi All, I've been using MS SQL 2k and it's bundled query analyzer SQL editor for more than 5 years. I am exploring postgres right now and I hit a snag I don't understand... MS SQL : debugging - use query analyzer, declare variables, plug in code and test... PG : using PGAdminIII editor : declare tst int; (or whatever variable) there is an error already....syntax error... I did some research and it seems that variables cannot be declared outside a function scope? Looking at functions within PG, the implementation looks like one massive dynamic sql string.... I wanted to figure out how PG handles single quotes, double quotes, string concats and such.... problem is that the editor won't even let me declare variables...ahmm help? how does one debug in PG? what tool? anything like query analyzer of MS SQL? why can't I declare variables and print the values out to check and debug? HELP? Many Thanks in advance...
On Nov 7, 2007 8:56 PM, Robert Bernabe <rbernabe@sandmansystems.com> wrote: > Hi All, > I've been using MS SQL 2k and it's bundled query analyzer SQL editor for > more than 5 years. I am exploring postgres right now and I hit a snag I > don't understand... > > MS SQL : debugging - use query analyzer, declare variables, plug in code > and test... > > PG : using PGAdminIII editor : > > declare > > tst int; (or whatever variable) > > there is an error already....syntax error... > > I did some research and it seems that variables cannot be declared > outside a function scope? Looking at functions within PG, the > implementation looks like one massive dynamic sql string.... > > I wanted to figure out how PG handles single quotes, double quotes, > string concats and such.... problem is that the editor won't even let me > declare variables...ahmm help? how does one debug in PG? what tool? > anything like query analyzer of MS SQL? why can't I declare variables > and print the values out to check and debug? HELP? Many Thanks in > advance... > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > I recommend starting with the documentation. It sounds like you'd like to do pl/pgsql stuff, so you might try this link: http://www.postgresql.org/docs/8.2/interactive/plpgsql.html. Take sample functions from there, and adapt them to do what you need. It's easier to take something that works and learn the postgresql idioms from that rather than to take something from MS SQL and try to replace MS SQL-isms with PostgreSQL-isms. - Josh/eggyknap
Thanks for the advice...although there is no choice but to try to port over since we have 3k MS Sql stored procs already in the production system...1st step seems to be to evaluate the performance of PG vs MS SQL in our systems and the only way to do that is to port the massive stored procs into PG and time the performance... Josh Tolley wrote: >On Nov 7, 2007 8:56 PM, Robert Bernabe <rbernabe@sandmansystems.com> wrote: > > >> >> > >I recommend starting with the documentation. It sounds like you'd like >to do pl/pgsql stuff, so you might try this link: >http://www.postgresql.org/docs/8.2/interactive/plpgsql.html. Take >sample functions from there, and adapt them to do what you need. It's >easier to take something that works and learn the postgresql idioms >from that rather than to take something from MS SQL and try to replace >MS SQL-isms with PostgreSQL-isms. > >- Josh/eggyknap > > > > >
On Nov 8, 2007 11:32 PM, Robert Bernabe <rbernabe@sandmansystems.com> wrote: > Thanks for the advice...although there is no choice but to try to port > over since we have 3k MS Sql stored procs already in the production > system...1st step seems to be to evaluate the performance of PG vs MS > SQL in our systems and the only way to do that is to port the massive > stored procs into PG and time the performance... At some point, porting functions directly will be the way to go. But make sure you've gotten used to pl/pgsql first, so you know what changes to make in the porting process. -Josh/eggyknap