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