RE: PGAdmin4 debugger - unable to call ltree functions

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(dot)klaver(at)aklaver(dot)com]
Sent: 26 April, 2018 17:23
To: ib(at)ianbellsoftware(dot)com <mailto:ib(at)ianbellsoftware(dot)com> ;
pgsql-general(at)lists(dot)postgresql(dot)org
<mailto:pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com> adrian(dot)klaver(at)aklaver(dot)com