Thread: select fails inside function, but works otherwise

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