Re: left() in postgres - Mailing list pgsql-general

Dear Peter,

Yes, indeed the substring() is a very good soloution. Actually it is the
only suitable function in the PG standard library that can help you.
Based on the substring we could make our own left() and right() functions
using pgplsql.  Comapre to substring(), they will only provide you a more
convinient interface, nothing more nothing less.

The two functions:

DROP FUNCTION left(TEXT, INTEGER);
CREATE FUNCTION left(TEXT, INTEGER) RETURNS TEXT AS '
DECLARE
BEGIN
    RETURN substr($1, 1, $2);
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION right(TEXT, INTEGER);
CREATE FUNCTION right(TEXT, INTEGER) RETURNS TEXT AS '
DECLARE
BEGIN
    RETURN substr($1, char_length($1) - $2 + 1, $2);
END;
' LANGUAGE 'plpgsql';

After when you are installed the plpgsql language extension and created the
two function above, we can use them in exactly the same way you did it in
MySQL.

For example:

sms=> select lastmsg, left(lastmsg, 2), right(lastmsg, 2) from usrprf limit
5;
            lastmsg            | left | right
-------------------------------+------+-------
 2002-07-29 18:50:20.237055-04 | 20   | 04
 2002-07-23 16:45:12.936491-04 | 20   | 04
 2002-08-01 12:18:23.278126-04 | 20   | 04
 2002-09-07 16:31:41.096087-04 | 20   | 04
 2002-08-23 09:21:42.927549-04 | 20   | 04
(5 rows)

If you think the preformance is not good or you don't want to use the
plpgsql, I can make the same function for you in "C". It is very easy based
on the PG-examples, but to install a "C" extension is more difficult.

Kind Regards,
Yuri

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Peter Nixon" <listuser@peternixon.net>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, October 25, 2002 9:36 AM
Subject: Re: [GENERAL] left() in postgres


>
> Try substring().
>
> --------------------------------------------------------------------------
-
>
> Peter Nixon wrote:
> > Hi Guys
> >
> > I am trying to do a query similar to the following:
> >
> > select left(field,3) from table;
> >
> > Now this works in Mysql/Access etc etc, but not in postgres
> > (I am running PostgreSQL 7.2)
> >
> > Can anyone suggest how to do this in postgres? I have a database with
many
> > millions of records, and this functionality is essential for me.
> >
> > PS. I did do a google search etc before posting.
> >
> > Regards
> >
> > --
> >
> > Peter Nixon
> > http://www.peternixon.net/
> > PGP Key: http://www.peternixon.net/public.asc
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly