Thread: PGAdmin4 debugger - unable to call ltree functions

On 04/26/2018 01:22 PM, Ian Bell wrote:
> I have recently started working with the PGAdmin4 debugger and have 
> encountered a particular problem when testing my own functions that, in 
> turn, call functions from the ltree module.   The sample code below 
> successfully runs in  PSQL.  However, it fails when run in the PGAdmin4 
> debugger.   Specifically, the debugger generates the following error 
> message when it executes the first call to ‘*text2ltree*’:
> 
> ERROR: syntax error at position 0
> 
> CONTEXT: SQL statement "SELECT exists ( select 1 from TableLtree where 
> MyPath = text2ltree( MyArg ) )"
> 
> PL/pgSQL function testltree(text) line 5 at IF
> 
> I have successfully been able to use the debugger to walk through many 
> of my test functions providing they only use standard SQL variables.   
> However, I am unable to debug code that calls the ltree functions.

Are they all using the 'IF exists ..' construct?

What if you try a very simple function, something like(not tested):

create or replace function TestLtree2(MyArg text)

returns void

as $$

declare

      _testvar integer;

begin

     SELECT 1 INTO _testvar FROM TableLtree WHERE MyPath = text2ltree( 
MyArg ) LIMIT 1;

end;

$$ language plpgsql;


> 
> Is this a problem/bug with the debugger or am I doing something wrong?  
> If I am doing something wrong then can you tell me what it is?
> 
> Thank you,
> 
> Ian
> 
> *_Sample Code in a SQL file:_*
> 
> create extension if not exists ltree;
> 
> create extension if not exists pldbgapi;
> 
> create table if not exists TableLtree(
> 
>       ID int
> 
>            primary key generated by default as identity,
> 
>       MyPath ltree
> 
> );
> 
> create or replace function TestLtree(
> 
>       MyArg text
> 
> )
> 
> returns void
> 
> as $$
> 
> declare
> 
>       status boolean;
> 
> begin
> 
>       if exists ( select 1 from TableLtree where MyPath = text2ltree( 
> MyArg ) ) then
> 
>             status := true;
> 
>       else
> 
>             status := false;
> 
>       end if;
> 
>       if status = false then
> 
>             insert into TableLtree( MyPath ) values ( text2ltree( MyArg ) );
> 
>       end if;
> 
> end;
> 
> $$ language plpgsql;
> 
> select * from TestLtree( 'a.b.c' );
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 04/26/2018 02:54 PM, Ian Bell wrote:
> Hello Adrian,
> 
> I just tried your suggestion and it generates the same error.  For 
> verification, here is the function run in the debugger.
> 
> create or replace function TestLtreeV2( MyArg text ) returns void
> 
> as $$
> 
> declare
> 
>       _testVar integer;
> 
> begin
> 
> SELECT 1 INTO _testvar FROM TableLtree WHERE MyPath = text2ltree( MyArg 
> ) LIMIT 1;
> 
> raise notice '_testVar = %', _testVar;
> 
> end;
> 
> $$ language plpgsql;

I'm tapped out. You might want to get hold of the pgAdmin folks directly:

https://www.pgadmin.org/support/list/


> 
> Ian


-- 
Adrian Klaver
adrian.klaver@aklaver.com

"Ian Bell" <ib@ianbellsoftware.com> writes:
> I just tried your suggestion and it generates the same error.  For
> verification, here is the function run in the debugger.

I just realized that "syntax error at position N" is *not* the exact
spelling you would get from a syntax error in PG's main parser, which
I think is what we were all assuming this'd trace down to somehow.

What it is is the (very poorly chosen) message you get if either
ltree_in() or lquery_in() doesn't like what it's been handed.
So basically, the problem here is that TestLtree() is being called
with a string that isn't a valid ltree representation.  You haven't
shown us what you are doing on that end, but for example I can
duplicate the error message with

# select text2ltree(' ');
ERROR:  syntax error at position 0

            regards, tom lane

My function TestLtree() is being executed in the PGAdmin debugger.   The
debugger requires I define the argument being passed, I set this to 'a.b.c'
and I have confirmed in the debugger that this is indeed what is passed into
the function.    

I do not understand what you mean by "You haven't shown us what you are
doing on that end".   I am only using the debugger to test a simple function
- nothing more.

As mentioned before, the code runs without problems in both PSQL and in
PGAdmin4 - it only fails when executed in the debugger.  

BTW, I'm running PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit
under Windows 10.

Thank,

Ian


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: 26 April, 2018 18:18
To: ib@ianbellsoftware.com
Cc: 'Adrian Klaver' <adrian.klaver@aklaver.com>;
pgsql-general@lists.postgresql.org
Subject: Re: PGAdmin4 debugger - unable to call ltree functions

"Ian Bell" <ib@ianbellsoftware.com> writes:
> I just tried your suggestion and it generates the same error.  For 
> verification, here is the function run in the debugger.

I just realized that "syntax error at position N" is *not* the exact
spelling you would get from a syntax error in PG's main parser, which I
think is what we were all assuming this'd trace down to somehow.

What it is is the (very poorly chosen) message you get if either
ltree_in() or lquery_in() doesn't like what it's been handed.
So basically, the problem here is that TestLtree() is being called with a
string that isn't a valid ltree representation.  You haven't shown us what
you are doing on that end, but for example I can duplicate the error message
with

# select text2ltree(' ');
ERROR:  syntax error at position 0

            regards, tom lane

"Ian Bell" <ib@ianbellsoftware.com> writes:
> My function TestLtree() is being executed in the PGAdmin debugger.   The
> debugger requires I define the argument being passed, I set this to 'a.b.c'
> and I have confirmed in the debugger that this is indeed what is passed into
> the function.    

> I do not understand what you mean by "You haven't shown us what you are
> doing on that end".   I am only using the debugger to test a simple function
> - nothing more.

Well, I'd say that *something* is calling that function with an argument
different from what you expect.  It's entirely possible that this is a
bug in PGAdmin's debugger.  Again, you'd be best off discussing it on the
pgadmin lists rather than here.

            regards, tom lane