Re: Isnumeric function? - Mailing list pgsql-sql

<p><font size="2">Thankyou all for your feedback. I actually only want to check for whole numbers, so the ~ '^[0-9]+$'
expressionis good.</font><p><font size="2">The issue really is that our CMS system sometimes holds the value of primary
keyswithin a "content" varchar column(don't ask!), which is a nightmare to search across. I tried applying an index
acrossthe "content" varchar column and it failed.</font><p>        <font size="2">error: btree item size 2744 exceeds
maximum2713.</font><p><font size="2">I assume I had to change some server settings to extend the maximum, however in
theend this column holds content, and even applying an index would be incredible slow to search across hundred of
thousandsof "content" records looking for a primary key.</font><p><font size="2">So I came up with the following. A
Insert/updatetrigger would call a procedure to check to see if the content is numeric(a whole number), if so would
updatean indexed integer column called (content_numeric). Which would be the base column to search appon.</font><br
/><p><fontsize="2">Here is the function anyway:</font><br /><font size="2">CREATE OR REPLACE FUNCTION
update_content_node()</font><br/><font size="2">  RETURNS trigger 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 /><font size="2">     NEW.content_numeric := NEW.content;</font><br /><font size="2"> 
ELSE</font><br/><font size="2">     NEW.content_numeric := null;</font><br /><font size="2">  END IF;</font><br /><font
size="2"> RETURN NEW;</font><br /><font size="2">end;</font><br /><font size="2">'</font><br /><font size="2"> 
LANGUAGE'plpgsql' IMMUTABLE;</font><br /><p><font size="2">Does anyone have any better suggestions???</font><p><font
size="2">Theo</font><br/><br /><table><tr><td bgcolor="#ffffff"><font
color="#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 the intended
recipientof this email, you must not<br />use, copy or disclose its content to anyone. You must not copy or <br
/>communicateto others content that is confidential or subject to <br />copyright, unless you have the consent of the
contentowner.<br /></font></td></tr></table>