Re: Isnumeric function?

Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:
> So I came up with the following. A Insert/update trigger would call a
> procedure to check to see if the content is numeric(a whole number), if so
> would update an indexed integer column called (content_numeric). Which would
> be the base column to search appon.

> CREATE OR REPLACE FUNCTION update_content_node()
>   RETURNS trigger AS
> '
> begin
>   /* New function body */
>   IF NEW.content ~ \'^[0-9]+$\' THEN
>      NEW.content_numeric := NEW.content;
>   ELSE
>      NEW.content_numeric := null;

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 thenIF NEW.content ~ \'^[0-9]{1,9}$\' THEN
Or use a bigint column and crank up the number of digits appropriately.
        regards, tom lane