Dear all,
I create a stored function to select some row and return it as cursor.
the function receive some parameter that later I put it in where clause
But I found that select statement runs Case Sensitive
so if I have a record, for example
- id -nama_pelanggan
- 1 - Wira
I wil get the result if I call it this way : select
penggunaselect('mycursor', 'Wi');
but I don't got any row if I call it like this : select
penggunaselect('mycursor', 'Wi');
I need the search to run in not-case-sensitive mode
so please if you have any suggestion
Thank you
Anyway, this is the code
CREATE OR REPLACE FUNCTION penggunaselect(curs refcursor, pnama
character varying, pgrup character varying, paktif integer)
RETURNS refcursor AS
$BODY$
declare
pid_grup integer := 0;
sqltext character varying = '';
begin
-- cari id grup
if pgrup <> '' then
select id into pid_grup from grup where nama_grup = pgrup;
end if;
sqltext = 'select p.nama_pengguna, g.nama_grup, p.login_terakhir,
p.mulai_dibuat, p.aktif, p.id
from pengguna p, grup g
where p.id_grup = g.id';
if pnama <> '' then
sqltext = sqltext || ' and p.nama_pengguna like ''%' || pnama || '%'' ';
end if;
if pid_grup <> 0 then
sqltext = sqltext || ' and p.id_grup = ' || pid_grup;
end if;
if paktif = 1 then
sqltext = sqltext || ' and p.aktif = true';
end if;
if paktif = 2 then
sqltext = sqltext || ' and p.aktif = false';
end if;
sqltext = sqltext || ' order by p.nama_pengguna';
open curs for execute(sqltext);
return curs;
end;
$BODY$
LANGUAGE 'plpgsql';