Greenplum Sorting Functions like Oracle

Post date: May 07, 2013 10:49:26 PM

create or replace function pgoramin

(

  is_val_1                varchar,

  is_val_2                varchar

)

returns varchar

as

$$

begin

  if (oracompat.nlssort(is_val_1,'C') >= oracompat.nlssort(is_val_2,'C')) then

    return is_val_2;

  else

    return is_val_1;

  end if;

end

$$

language plpgsql immutable strict;

create or replace function pgoramax

(

  is_val_1                varchar,

  is_val_2                varchar

)

returns varchar

as

$$

begin

  if (oracompat.nlssort(is_val_1,'C') >= oracompat.nlssort(is_val_2,'C')) then

    return is_val_1;

  else

    return is_val_2;

  end if;

end

$$

language plpgsql immutable strict;

drop aggregate if exists oracharmax(varchar);

create aggregate oracharmax (varchar)

(

  sfunc = pgoramax,

  stype = varchar,

  prefunc = pgoramax

);

drop aggregate if exists oracharmin(varchar);

create aggregate oracharmin (varchar)

(

  sfunc = pgoramin,

  stype = varchar,

  prefunc = pgoramin

);