Re: select return case sensitive record

Sorry, forgot to add subject

On 12/5/08, hendra kusuma <penguinroad(at)gmail(dot)com> wrote:
> 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';
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>