RE: PGAdmin4 debugger - unable to call ltree functions - Mailing list pgsql-general

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.klaver@aklaver.com]
Sent: 26 April, 2018 17:23
To: ib@ianbellsoftware.com; 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

adrian.klaver@aklaver.com