Re: PGAdmin4 debugger - unable to call ltree functions

[Tom Lane]

"Ian Bell"  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

Re: PGAdmin4 debugger - unable to call ltree functions

[Tom Lane]

"Ian Bell"  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

Re: PGAdmin4 debugger - unable to call ltree functions

[Adrian Klaver]

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.kla...@aklaver.com

RE: PGAdmin4 debugger - unable to call ltree functions

[Ian Bell]

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;

 

Ian

 

 

 

 

 

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: 26 April, 2018 17:23
To: i...@ianbellsoftware.com <mailto:i...@ianbellsoftware.com> ;
pgsql-general@lists.postgresql.org
<mailto:pgsql-general@lists.postgresql.org> 
Subject: Re: 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

 <mailto:adrian.kla...@aklaver.com> adrian.kla...@aklaver.com

Re: PGAdmin4 debugger - unable to call ltree functions

[Adrian Klaver]

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.kla...@aklaver.com