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
adrian(dot)klaver(at)aklaver(dot)com