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