Thread: Interesting abilities of substring

Here is something cool that I did not realize postgres's substring()
could do.

Basically, it "knows what you mean" when you do substrings on dates and
numbers, doing an implicit cast for you. This is really nice if you
happen to be writing a generalized search system, as it makes the code
significantly simpler...

That's all, now back to our regularly scheduled mailing list.

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com    www.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

Sure, we use a user interface widget called "Ajax Dynamic List" from
www.dhtmlgoodies.com.  This replaces the HTML SELECT element.

When a user is sitting on a foreign-key field, such as a PATIENT or
CUSTOMER field, the user can just start typing letters or numbers.  An
AJAX call is made to the back end which searches all columns in a
pre-defined list.  If the list is first_name,last_name,dob, and they
type "ken" they will get everybody whose first name or last name begins
with 'ken', and the date is ignored.  If they type '1991' they will get
everybody born in 1991, and of course '1991-10' returns everybody born
in October 1991.

So it turns out that both string concatenation and substring do some
nifty implicit typing.  This means making the generalized lookup
requires you only to know the list of columns, without also needing to
know their types.

SELECT patient as _value
      ,patient
        || ' - ' || namlst
        || ' - ' || nam1st
        || ' - ' || phone
        || ' - ' || ssn
        || ' - ' || dob as _display   FROM patients WHERE (
SUBSTRING(LOWER(patient) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(namlst) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(nam1st) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(phone) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(ssn) FROM 1 FOR 2)='ke'
       OR SUBSTRING(LOWER(dob) FROM 1 FOR 2)='ke') ORDER BY patient
Limit 20

Eventually I will be driven to optimize that, but on tables < 10,000
rows it is still well within the threshhold of the human attention span
of < .5 second or so.

Naz Gassiep wrote:
> This may be useful to me and others, care to paste an example of what
> you mean?
> Thanks,
> - Naz.
>
> Kenneth Downs wrote:
>> Here is something cool that I did not realize postgres's substring()
>> could do.
>>
>> Basically, it "knows what you mean" when you do substrings on dates
>> and numbers, doing an implicit cast for you. This is really nice if
>> you happen to be writing a generalized search system, as it makes the
>> code significantly simpler...
>>
>> That's all, now back to our regularly scheduled mailing list.
>>


--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com    www.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

Kenneth Downs <ken@secdat.com> writes:
> Basically, it "knows what you mean" when you do substrings on dates and
> numbers, doing an implicit cast for you.

Implicit casts to text are evil, and are mostly going to be gone in 8.3.
So try not to rely on this behavior ...

            regards, tom lane

Tom Lane wrote:

Kenneth Downs <ken@secdat.com> writes: 

Basically, it "knows what you mean" when you do substrings on dates and 
numbers, doing an implicit cast for you.   

Implicit casts to text are evil, and are mostly going to be gone in 8.3.
So try not to rely on this behavior ... 

Based on general principle, or on specific bad things like unexpected or ill-defined results?

		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings 

-- 
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com    www.andromeda-project.org
631-689-7200   Fax: 631-689-0527
cell: 631-379-0010

Kenneth Downs <ken@secdat.com> writes:
> Tom Lane wrote:
>> Implicit casts to text are evil, and are mostly going to be gone in 8.3.
>> So try not to rely on this behavior ...

> Based on general principle, or on specific bad things like unexpected or
> ill-defined results?

Both.  Check the archives, and you'll see reports every few months from
people who got bit by unexpected misinterpretations of queries.  The
typical case is someone who tries to compare values of two different
datatypes, and the most plausible interpretation the parser can find
is to implicitly cast them both to text and use text comparison :-(.
Another thing we've seen happen is that someone thinks he's invoking
substring(text,int), but his second argument isn't int but float or
numeric, and instead of an error he gets substring(text,text) which
does Something Completely Different from what he expects.

The problem with an implicit cast is basically that it'll kick in when
you didn't want or expect it.  So in CVS HEAD most casts to text are not
implicit anymore, and we've put in some much more limited flexibility:
the || operator will take any data type and do an implicit coercion to
text, so long as at least one of the inputs is of a string type.  (We
had some problems not breaking the array interpretation of ||, but
surmounted those.)  It might be possible to do something similar for
substring, but I'm not sure how to put any flexibility there without
re-introducing the problem alluded to above.

            regards, tom lane