stores_demo -  functions -  systdist

Description

none

Function properties

name	value
name	systdist
created
type	SQL scalar function

Parameters

none

Usage

set @myvar = systdist

Code

create dba procedure informix.systdist (table_id int, column_no int)
   returning int, datetime year to fraction (5), char(1), 
             smallfloat, smallfloat, float , stat, char(1);

   define v_tabauth      char(8);
   define v_colauth      char(3);
   define is_allowed     int;
   define search_columns int;
   define v_colno        smallint;
   define v_seqno        int;
   define v_constr_time  datetime year to fraction(5);
   define v_mode         char(1);
   define v_resolution   smallfloat;
   define v_confidence   smallfloat;
   define v_encdat       stat;
   define v_owner        char(8);
   define user           procedure;
   define v_stattype     char(1);
   define v_smplsize     float;

-- First verify that the current user has select privileges on this column

   let is_allowed = 0;
   let search_columns = 0;

-- Check sysusers to see if the usertype is 'D', ie., the
-- current user has dba privileges and may see any columns.
   select usertype
      into v_mode
      from informix.sysusers
      where username = user;
   if v_mode = 'D' then
      let is_allowed = 1;
   else
      -- See if the user owns the table, and therefore can see the columns.
      select owner
         into v_owner
     from informix.systables
     where tabid = table_id;
      if v_owner = user then
         let is_allowed = 1;
      end if
   end if

   if is_allowed = 0 then
      foreach 
         select tabauth 
            into v_tabauth
            from informix.systabauth
            where tabid = table_id and
                  (grantee = user or
                   grantee = 'public')
         if substr(v_tabauth, 1, 1) = 's' or substr(v_tabauth, 1, 1) = 'S' then
            let is_allowed = 1;
            exit foreach;
         elif substr(v_tabauth, 3, 1) = '*' then
            let search_columns = 1;
         end if
      end foreach
   end if

-- Search syscolauth only if user does not have select 
-- privileges on all columns.  If the user has no select
-- privileges on any column, then we need search no further.

   if is_allowed = 0 and search_columns = 1 then
      foreach
         select colauth
            into v_colauth
            from informix.syscolauth
            where tabid = table_id and
                  colno = column_no and
                  (grantee = user or
                   grantee = 'public')
         if substr(v_colauth, 1, 1) = 's' or substr(v_colauth, 1, 1) = 'S' then
            let is_allowed = 1;
            exit foreach;
         end if
      end foreach
   end if

-- Return with no rows found if not allowed to select from
-- the column designated by (tabid,colno).

   if is_allowed = 0 then
      raise exception -272;
   end if

-- Now find the distribution rows

   foreach
      select  seqno, constr_time, mode, 
              resolution, confidence, smplsize, encdat, type
         into v_seqno, v_constr_time, v_mode, 
              v_resolution, v_confidence, v_smplsize, v_encdat, v_stattype
         from informix.sysdistrib
         where tabid = table_id and
               colno = column_no
         order by seqno
      return v_seqno, v_constr_time, v_mode, 
             v_resolution, v_confidence, v_smplsize, v_encdat, v_stattype
         with resume;
   end foreach

-- Engine will return 100 to user

end procedure;