Greenplum Sorting Functions like Oracle

posted Apr 29, 2017, 1:23 AM by Sachchida Ojha
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
);
Comments