Is there a way to debug a PL/pgSQL function? It's behaving very irradic!

I have two function, one that works and one that doesn't. The part that
don't work in func2 is 'SELECT INTO ... ...' and I can't figgure out
why it doesnt't work!

-- 
 Turbo     __ _     Debian GNU     Unix _IS_ user friendly - it's just 
 ^^^^^    / /(_)_ __  _   ___  __  selective about who its friends are 
         / / | | '_ \| | | \ \/ /   Debian Certified Linux Developer  
  _ /// / /__| | | | | |_| |>  <  Turbo Fredriksson   turbo@tripnet.se
  \\\/  \____/_|_| |_|\__,_/_/\_\ Stockholm/Sweden

colonel fissionable assassination Cuba World Trade Center strategic
smuggle congress Rule Psix cryptographic SDI critical Kennedy Semtex
FBI
[See http://www.aclu.org/echelonwatch/index.html for more about this]

25 Jul 2001 15:46:27 +0200
Turbo Fredriksson <turbo@bayour.com>
plpgsql: Debug function?
On 25 Jul 2001, Turbo Fredriksson wrote:

> Is there a way to debug a PL/pgSQL function? It's behaving very irradic!

It's crude, but you can output debugging statements w/ RAISE NOTICE
or catch flawed assumptions by RAISE EXCEPTION.

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington


Wed, 25 Jul 2001 11:13:34 -0400 (EDT)
Joel Burton <jburton@scw.org>
Re: plpgsql: Debug function?
>>>>> "Joel" == Joel Burton <jburton@scw.org> writes:

    Joel> On 25 Jul 2001, Turbo Fredriksson wrote:
    >> Is there a way to debug a PL/pgSQL function? It's behaving very
    >> irradic!

    Joel> It's crude, but you can output debugging statements w/ RAISE
    Joel> NOTICE or catch flawed assumptions by RAISE EXCEPTION.

That's what I've been doing...

The problem is that a 'SELECT INTO ...' in the function don't work, but
the actual SELECT in psql works fine! 

The variable I'm SELECT'ing into don't get initialized...

-- 
 Turbo     __ _     Debian GNU     Unix _IS_ user friendly - it's just 
 ^^^^^    / /(_)_ __  _   ___  __  selective about who its friends are 
         / / | | '_ \| | | \ \/ /   Debian Certified Linux Developer  
  _ /// / /__| | | | | |_| |>  <  Turbo Fredriksson   turbo@tripnet.se
  \\\/  \____/_|_| |_|\__,_/_/\_\ Stockholm/Sweden

radar Marxist counter-intelligence 747 Kennedy Serbian CIA NSA SEAL
Team 6 FSF [Hello to all my fans in domestic surveillance] Clinton FBI
Soviet class struggle
[See http://www.aclu.org/echelonwatch/index.html for more about this]

25 Jul 2001 17:18:31 +0200
Turbo Fredriksson <turbo@bayour.com>
Re: plpgsql: Debug function?
On 25 Jul 2001, Turbo Fredriksson wrote:

> >>>>> "Joel" == Joel Burton <jburton@scw.org> writes:
> 
>     Joel> On 25 Jul 2001, Turbo Fredriksson wrote:
>     >> Is there a way to debug a PL/pgSQL function? It's behaving very
>     >> irradic!
> 
>     Joel> It's crude, but you can output debugging statements w/ RAISE
>     Joel> NOTICE or catch flawed assumptions by RAISE EXCEPTION.
> 
> That's what I've been doing...
> 
> The problem is that a 'SELECT INTO ...' in the function don't work, but
> the actual SELECT in psql works fine! 
> 
> The variable I'm SELECT'ing into don't get initialized...

Can you post a simple, reproducible example?

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington


Wed, 25 Jul 2001 11:25:56 -0400 (EDT)
Joel Burton <jburton@scw.org>
Re: plpgsql: Debug function?
> Is there a way to debug a PL/pgSQL function? It's behaving very
> irradic!

>     Joel> It's crude, but you can output debugging statements w/ RAISE
>     Joel> NOTICE or catch flawed assumptions by RAISE EXCEPTION.

Also try turning on query logging, so you can see in the postmaster
log the queries plpgsql is sending to the SQL engine.  This is especially
helpful for catching unexpected substitutions or lack of substitutions
of plpgsql variables, as in Morgan Curley's recent problem over in
pgsql-sql.

A volunteer to improve plpgsql's debugging/tracing facilities would
be favorably received...

			regards, tom lane

Wed, 25 Jul 2001 11:46:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: plpgsql: Debug function? 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    >> Is there a way to debug a PL/pgSQL function? It's behaving very
    >> irradic!

    Joel> It's crude, but you can output debugging statements w/ RAISE
    Joel> NOTICE or catch flawed assumptions by RAISE EXCEPTION.

    Tom> Also try turning on query logging, so you can see in the
    Tom> postmaster log the queries plpgsql is sending to the SQL
    Tom> engine.

Would that be the 'debug_print_query = true' in posgresql.conf?

It IS true, but still nothing in the syslog...

-- 
 Turbo     __ _     Debian GNU     Unix _IS_ user friendly - it's just 
 ^^^^^    / /(_)_ __  _   ___  __  selective about who its friends are 
         / / | | '_ \| | | \ \/ /   Debian Certified Linux Developer  
  _ /// / /__| | | | | |_| |>  <  Turbo Fredriksson   turbo@tripnet.se
  \\\/  \____/_|_| |_|\__,_/_/\_\ Stockholm/Sweden

PLO Legion of Doom domestic disruption Clinton spy Rule Psix Nazi
quiche radar fissionable BATF SDI bomb security NSA
[See http://www.aclu.org/echelonwatch/index.html for more about this]

26 Jul 2001 12:16:52 +0200
Turbo Fredriksson <turbo@bayour.com>
Re: Re: plpgsql: Debug function?
On 26 Jul 2001, Turbo Fredriksson wrote:

> >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> Would that be the 'debug_print_query = true' in posgresql.conf?
>
> It IS true, but still nothing in the syslog...

I'm not sure. But I expect the logging would go out the postmaster's logs,
not necessarily syslog.

Take care,

Bill


Fri, 27 Jul 2001 17:09:50 -0700 (PDT)
Bill Studenmund <wrstuden@zembu.com>
Re: Re: plpgsql: Debug function?