Re: [PERFORM] create index with substr function

2004-10-21 Thread george young 
As previously suggested by Stephan Szabo, you need to create a helper
function, e.g.:
create or replace function after9(text)returns text language plpgsql immutable as '
   begin
   return substr($1, 10);
   end;
';

You may need the "immutable" specification is to allow the
function's use in an index.

Then use this function in the index creation:

CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (after9(doc_urn));

I think that should do it.


-- George
>
On Thu, 21 Oct 2004 11:37:26 +0800
"Ray" <[EMAIL PROTECTED]> threw this fish to the penguins:

> sorry it doesn't works, as my postgres is 7.3 not 7.4. any other alternative
> solution for version after 7.4??
> 
> Thank
> Ray : )
> 
> - Original Message - 
> From: "Rosser Schwarz" <[EMAIL PROTECTED]>
> To: "Ray" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, October 21, 2004 11:34 AM
> Subject: Re: [PERFORM] create index with substr function
> 
> 
> > while you weren't looking, Ray wrote:
> >
> > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree
> (SUBSTR(doc_urn,10));
> >
> > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree
> ((SUBSTR(doc_urn,10)));
> >
> > You need an additional set of parens around the SUBSTR() call.
> >
> > /rls
> >
> > -- 
> > :wq
> >
> 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 


-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] create index with substr function

2004-10-20 Thread Joshua D. Drake 




Tom Lane wrote:

  "Ray" <[EMAIL PROTECTED]> writes:
  
  
CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10));
ERROR:  parser: parse error at or near "10" at character 68

  
  
This will work in 7.4, but not older releases.

  

Can't you just use a SQL function that calls the substr function? I
have done that with date functions before
like:
CREATE OR REPLACE FUNCTION get_month(text) returns double precision AS '
   SELECT date_part('month',$1);
' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX get_month_idx on foo(get_month(date_field));
Or in this case:

CREATE OR REPLACE FUNCTION sub_text(text) returns text AS '
  SELECT SUBSTR($1,10) from foo;
' LANGUAGE 'SQL' IMMUTABLE;

CREATE INDEX sub_text_idx ON foo(sub_text(doc_urn));

This works on 7.3.6???

Sincerely,

Joshua D. Drake





  			regards, tom lane

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



-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Re: [PERFORM] create index with substr function

2004-10-20 Thread Rosser Schwarz 
while you weren't looking, Ray wrote:

> CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10));

CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree ((SUBSTR(doc_urn,10)));

You need an additional set of parens around the SUBSTR() call.

/rls

-- 
:wq

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] create index with substr function

2004-10-20 Thread Ray 
sorry it doesn't works, as my postgres is 7.3 not 7.4. any other alternative
solution for version after 7.4??

Thank
Ray : )

- Original Message - 
From: "Rosser Schwarz" <[EMAIL PROTECTED]>
To: "Ray" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, October 21, 2004 11:34 AM
Subject: Re: [PERFORM] create index with substr function


> while you weren't looking, Ray wrote:
>
> > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree
(SUBSTR(doc_urn,10));
>
> CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree
((SUBSTR(doc_urn,10)));
>
> You need an additional set of parens around the SUBSTR() call.
>
> /rls
>
> -- 
> :wq
>


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] create index with substr function

2004-10-20 Thread Stephan Szabo 

On Thu, 21 Oct 2004, Ray wrote:

> Hi All,
>
> I have a table in my postgres:
> Table: doc
>  Column |Type | Modifiers
>  ---+-+---
>  doc_id  | bigint  | not null
>  comp_grp_id | bigint  | not null
>  doc_type  | character varying(10)| not null
>  doc_urn| character varying(20)| not null
>
> I want to create an index on doc_urn column with using substr function like this:
> CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10));
>
> but there is an error:
>
> ERROR:  parser: parse error at or near "10" at character 68
>
> what's wrong for this SQL? As I have found some reference on the
> internet, I can't find anything wrong in this SQL.

What version are you using? If you're using anything previous to 7.4 then
the above definately won't work and the only work around I know of is to
make another function which takes only the column argument and calls
substr with the 10 constant.


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] create index with substr function

2004-10-20 Thread Tom Lane 
"Ray" <[EMAIL PROTECTED]> writes:
> CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10));
> ERROR:  parser: parse error at or near "10" at character 68

This will work in 7.4, but not older releases.

regards, tom lane

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

Re: [PERFORM] create index with substr function

2004-10-20 Thread Ray 
Thank you all kindly response. : )

I am currently using postgres 7.3, so any example or solution for version
after 7.4 if i want to create an index with substr function???

Thanks,
Ray


- Original Message - 
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Ray" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, October 21, 2004 10:57 AM
Subject: Re: [PERFORM] create index with substr function


>
> On Thu, 21 Oct 2004, Ray wrote:
>
> > Hi All,
> >
> > I have a table in my postgres:
> > Table: doc
> >  Column |Type | Modifiers
> >  ---+-+---
> >  doc_id  | bigint  | not null
> >  comp_grp_id | bigint  | not null
> >  doc_type  | character varying(10)| not null
> >  doc_urn| character varying(20)| not null
> >
> > I want to create an index on doc_urn column with using substr function
like this:
> > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree
(SUBSTR(doc_urn,10));
> >
> > but there is an error:
> >
> > ERROR:  parser: parse error at or near "10" at character 68
> >
> > what's wrong for this SQL? As I have found some reference on the
> > internet, I can't find anything wrong in this SQL.
>
> What version are you using? If you're using anything previous to 7.4 then
> the above definately won't work and the only work around I know of is to
> make another function which takes only the column argument and calls
> substr with the 10 constant.
>


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]