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

I was going to suggest this and decided to do a little testing before
and found that the left function was much slower that just using the
substr() function.  I have not used function much.  Is it often the case
that functions are slower when doing simple things like providing
syntactic sugar?

David Blood
Matraex, Inc


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gyorgy Molnar
Sent: Friday, October 25, 2002 3:58 PM
To: Bruce Momjian; Peter Nixon
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] left() in postgres

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html