Thread: Re: Very Slow Query

Re: Very Slow Query

From
Josh Berkus
Date:
Chad,

> CREATE OR REPLACE FUNCTION phonenum_substr (varchar)
> RETURNS VARCHAR AS '
> SELECT substr(cr.phonenum, 1, 6) as phonenum
>
> --is cr.phonenum supposed to be $1?
>
> from call_results_fixed cr;
> ' LANGUAGE 'sql' WITH (ISCACHABLE, ISSTRICT);

OOOps!  Sorry, got confused:

CORRECT Function is:

CREATE OR REPLACE FUNCTION phonenum_substr (varchar)
RETURNS VARCHAR AS '
SELECT substr($1, 1, 6);
 ' LANGUAGE 'sql' WITH (ISCACHABLE, ISSTRICT);

-Josh

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: Very Slow Query

From
"Chad Thompson"
Date:
Cool, I got it to work!
I also changed the syntax of the index that you gave me to make use of the
function

CREATE INDEX idx_cr_phonenum_substr ON
call_results_fixed(phonenum_substr(phonenum));

Thanks for everything
Chad

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Chad Thompson" <chad@weblinkservices.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Wednesday, July 31, 2002 12:27 PM
Subject: Re:[NOVICE] Very Slow Query



Chad,

> CREATE OR REPLACE FUNCTION phonenum_substr (varchar)
> RETURNS VARCHAR AS '
> SELECT substr(cr.phonenum, 1, 6) as phonenum
>
> --is cr.phonenum supposed to be $1?
>
> from call_results_fixed cr;
> ' LANGUAGE 'sql' WITH (ISCACHABLE, ISSTRICT);

OOOps!  Sorry, got confused:

CORRECT Function is:

CREATE OR REPLACE FUNCTION phonenum_substr (varchar)
RETURNS VARCHAR AS '
SELECT substr($1, 1, 6);
 ' LANGUAGE 'sql' WITH (ISCACHABLE, ISSTRICT);

-Josh

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco