Re: Isnumeric function? - Mailing list pgsql-sql

<p><font size="2">Thanks Tom,</font><br /><font size="2">Actually I did not attach the latest function, I did have a
limitof 9 numerical characters, found that out when I applied the update to move all current numerical values to that
column.</font><p><fontsize="2">Theo</font><p><font size="2">-----Original Message-----</font><br /><font size="2">From:
TomLane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>] </font><br /><font size="2">Sent: Thursday, 9
September2004 9:57 AM</font><br /><font size="2">To: Theo Galanakis</font><br /><font size="2">Cc:
pgsql-sql@postgresql.org</font><br/><font size="2">Subject: Re: [SQL] Isnumeric function? </font><br /><p><font
size="2">TheoGalanakis <Theo.Galanakis@lonelyplanet.com.au> writes:</font><br /><font size="2">> So I came up
withthe following. A Insert/update trigger would call a </font><br /><font size="2">> procedure to check to see if
thecontent is numeric(a whole number), </font><br /><font size="2">> if so would update an indexed integer column
called(content_numeric). </font><br /><font size="2">> Which would be the base column to search
appon.</font><p><fontsize="2">> CREATE OR REPLACE FUNCTION update_content_node()</font><br /><font size="2">>  
RETURNStrigger AS</font><br /><font size="2">> '</font><br /><font size="2">> begin</font><br /><font
size="2">>  /* New function body */</font><br /><font size="2">>   IF NEW.content ~ \'^[0-9]+$\' THEN</font><br
/><fontsize="2">>      NEW.content_numeric := NEW.content;</font><br /><font size="2">>   ELSE</font><br /><font
size="2">>     NEW.content_numeric := null;</font><p><font size="2">Hmm.  Seems like you could get burnt by
"content"that is by chance a long string of digits --- you'd get an integer overflow error at the attempt to assign to
content_numeric. Can you make an assumption that indexable keys are at most 9 digits?  If so then</font><p>       
<fontsize="2">IF NEW.content ~ \'^[0-9]{1,9}$\' THEN</font><br /><font size="2">Or use a bigint column and crank up the
numberof digits appropriately.</font><p>                        <font size="2">regards, tom lane</font><table><tr><td
bgcolor="#ffffff"><fontcolor="#000000">______________________________________________________________________<br />This
email,including attachments, is intended only for the addressee<br />and may be confidential, privileged and subject to
copyright.If you<br />have received this email in error, please advise the sender and delete<br />it. If you are not
theintended recipient of this email, you must not<br />use, copy or disclose its content to anyone. You must not copy
or<br />communicate to others content that is confidential or subject to <br />copyright, unless you have the consent
ofthe content owner.<br /></font></td></tr></table>