MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2014

SQL CREATE FUNCTION uses schema from create time



fixed

7.1 TR7

When I create an SQL function using SQL naming, it appears to remember
the CURRENT SCHEMA that was in effect when the function was created
rather than the one that is in effect when the function executes.

Inside the function is an unqualified SELECT statement. My reading of
the manual indicates that an unqualified table reference in SQL naming
would use the CURRENT SCHEMA. Here is the test environment, created in
IBM i Navigator in SQL naming:

create table buck.sqltest (text char(10));
create table buck00001.sqltest (text char(10));

insert into buck.sqltest values('Production');
insert into buck00001.sqltest values('Test');

set schema buck;
create or replace function buck.sqltest() returns varchar(50)
language sql
begin
declare rtnval varchar(50);
select text into rtnval from sqltest;
set rtnval = rtnval concat ' CURRENT SCHEMA:' concat current schema;
return(rtnval);
end;

set path buck;
set schema buck;
select sqltest() from sysibm.sysdummy1;
-- returns 'Production CURRENT SCHEMA:BUCK'

set schema buck00001;
select sqltest() from sysibm.sysdummy1;
-- returns 'Production CURRENT SCHEMA:BUCK00001'

-- Now recreate the function with the test schema as CURRENT
set schema buck00001;
create or replace function buck.sqltest() returns varchar(50)
language sql
begin
declare rtnval varchar(50);
select text into rtnval from sqltest;
set rtnval = rtnval concat ' CURRENT SCHEMA:' concat current schema;
return(rtnval);
end;

set path buck;
set schema buck;
select sqltest() from sysibm.sysdummy1;
-- returns 'Test CURRENT SCHEMA:BUCK'

set schema buck00001;
select sqltest() from sysibm.sysdummy1;
-- returns 'Test CURRENT SCHEMA:BUCK00001'

I know that if I change my CREATE FUNCTION time naming to *SYS that the
function will use *LIBL to find the table (as expected) but I am
surprised that the function does not use the CURRENT SCHEMA at runtime
to locate the table.

I must be overlooking something but my hours in the manual haven't
revealed it and searching the internet has a high noise to signal ratio.
--buck





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact