How to set default schema for PostgreSQL debugger?

Question

I have 3 schemas: public, meta, data.

While debugging large function from "data" schema in PgAdmin III or SQL Manager for PostgreSQL, debuggers doesn't see any tables or functions from the same schema.

Cause function is large and depends on others functions it's not effective to use raise notice or writing schema prefix.

How can I set default schema to debug it correctly? I guess public schema is default.

Answer 1

you should set the 'search_path' to have all schemas needed if you don't prefix them. The default value is: "$user", public.

You should probably have: meta, data, public.

See: 18.11.1. Statement Behavior

Answer 2

If you are using pgAdmin, there are two ways to invoke the debugger.

First, you can right-click on a function and choose "Debug" . When you use this method, pgAdmin will prompt you for values for any IN or INOUT parameters, set a breakpoint on the function you've chosen, and then invoke that function for you (using the parameter values that you've supplied). In this case, you must ensure that search_path is set before you start the debugger. You can set the search_path for an entire database using the ALTER DATABASE...SET command, or for a specific user with the ALTER USER...SET command. Another option is to assign a value to search_path for the function you want to debug; see the ALTER FUNCTION...SET clause.

The other method is to right-click on a function name and choose "Set Breakpoint". When you use this method, the debugger will activate when any process invokes that function. Once you set a breakpoint, pgAdmin will pause, waiting for some other session to invoke the function in which you are interested. So start up another session, set the search_path as appropriate and then run your function. When the function starts, the debugger will activate in your pgAdmin session.

The nice thing about in-context debugging is that you can just run your application as normal and the debugger will activate whenever your application trips across the function, even if that function invocation is buried deep within some other function (or operator, or trigger, or ...)