Re: Can't get the debugger going - Mailing list pgadmin-support

On 13/04/2009 18:27, schatz wrote:
> - I enabled the option "Show system objects in the treeview" to see the
> templates and this is done, thank you.Templates don't have any functions in
> them?

Not unless you put them there. They really are "templates" - when you
create a new database, it's created as a copy of template1 (in fact, you
can use any database as a template, but the default is template1), and
template0 is there to recreate template1 is you somehow mess up the latter.

> - My platform is win XP. I created three schemas, each have different
> tables. One schema will create a table with the data from the other tables
> that resides in other two schemas. I need to write a cursor-loop-fetch
> function to extract data from some fields from the two schemas and insert
> into a new table in the third schema...I wrote a test function but can't
> debug it and not sure if I should use sql or pl/pgsql.  

Well, pl/pgsql gives you all the expressiveness you'd expect of a
procedural language, whereas SQL is just, well, SQL. :-) Use pl/pgsql if
you need all the control structures, etc, but be aware that there is
some performance penalty when the language is loaded at runtime.

> This is a copy from Function treeview, not sure why it writes two headers
> and adds more stuff to the bottom.

Define "more stuff".

> CREATE OR REPLACE FUNCTION "MySchema".create_data()
>   RETURNS void AS
> $BODY$CREATE FUNCTION "MySchema".create_data() RETURNS varchar(3) AS '

[snip]

You've got something really weird here - you've got an SQL function
which creates a pl/pgsql function! You don't need the outer SQL
function. Also, use dollar-quoting in the function definition; it saves
you having to double up all the apostrophes. Something like this:
 create or replace function "MySchema".create_data() returns varchar(3) as $$ declare   .... begin   ....   raise info
'prov';  raise info '----';   .... end; $$ language plpgsql;
 

> - I tried to install the debugger by running the command you recommended in
> the cmd window. Now when I run "C:...PostgreSQL\8.3\share\contrib\psql -f
> pldbgapi.sql my_dbase" it says: "psql is not recognized as an internal or
> external command, operable or batch file" Is that mean I don't have psql.exe
> installed? I see it under the bin folder,

As you say, it's in the bin directory, so you have to supply that path
to Windows when you run it:
 c:\...\8.3\bin\psql -f c:\...\8.3\share\pldbgapi.sql my_dbase

You might have to double-quote the paths if they have spaces.

> when I run it from the cmd line it
> asks me for a password. I tried the password for windows and postgresql both
> don't work.

In that case, you have to specify the PostgreSQL user (not the Windows
user) as which you want to connect, and then it's the password for that
user you have to supply. Add the -U option to the command line above:
 "c:\...\psql" -f "c:\...\pldbgapi.sql" -U [postgres user] my_dbase

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------