I've encountered a strange problem with some code that I'm porting
from oracle. When I do a select inside of a function it returns
nulls, but if I do the same select from psql it works fine. Here is
the test case that reproduces the problem:
drop table acs_objects;
create table acs_objects (
object_id integer not null
constraint acs_objects_pk primary key,
context_id integer constraint acs_objects_context_id_fk
references acs_objects(object_id),
security_inherit_p boolean default 't' not null
);
insert into acs_objects values (0,null,'t');
insert into acs_objects values (5,null,'t');
insert into acs_objects values (44,5,'t');
drop function acs_object__check_path (integer,integer);
create function acs_object__check_path (integer,integer)
returns boolean as '
declare
check_path__object_id alias for $1;
check_path__ancestor_id alias for $2;
context_id acs_objects.context_id%TYPE;
security_inherit_p acs_objects.security_inherit_p%TYPE;
begin
raise notice ''check path: % %'', check_path__object_id,
check_path__ancestor_id;
if check_path__object_id = check_path__ancestor_id then
return ''t'';
end if;
-- This select is failing. It does not return the correct value
-- when object_id = 44
select context_id, security_inherit_p
into context_id, security_inherit_p
from acs_objects
where object_id = check_path__object_id;
raise notice ''check path context : % %'', context_id,security_inherit_p;
if context_id is null or security_inherit_p = ''f'' then
context_id := 0;
end if;
raise notice ''check path recurse : % %'', context_id,check_path__ancestor_id;
return acs_object__check_path(context_id, check_path__ancestor_id);
end;' language 'plpgsql';
select acs_object__check_path(44,5);
Because of the select failure and a bug in this code, the function will
recurse until the back-end crashes. Of course if the select were
working properly that wouldn't happen. Here is the output from the
test run:
%psql -f tst.sql acspg
DROP
psql:tst.sql:10: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'acs_objects_pk' for table 'acs_objects'
psql:tst.sql:10: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT 125290 1
INSERT 125291 1
INSERT 125292 1
DROP
CREATE
psql:tst.sql:49: NOTICE: check path: 44 5
psql:tst.sql:49: NOTICE: check path context : <NULL> <NULL>
psql:tst.sql:49: NOTICE: check path recurse : 0 5
psql:tst.sql:49: NOTICE: check path: 0 5
psql:tst.sql:49: NOTICE: check path context : <NULL> <NULL>
... [snipped] ...
psql:tst.sql:49: NOTICE: check path: 0 5
psql:tst.sql:49: NOTICE: check path context : <NULL> <NULL>
psql:tst.sql:49: NOTICE: check path recurse : 0 5
psql:tst.sql:49: NOTICE: chec^CCancel request sent
psql:tst.sql:49: ERROR: Query was cancelled.
And here is the select from psql:
acspg=# select context_id, security_inherit_p from acs_objects where object_id = 44;
context_id | security_inherit_p
------------+--------------------
5 | t
(1 row)
acspg=#
