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----- 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 |
