# Custom Indexes

**Example :** 

CREATE a index for social security number , which is the the format of 1111-222-nnnn, you have to index the last 4 characters

```sql
CREATE TABLE if not exists ssn
(
    ssn text
);

INSERT INTO ssn (ssn)
values ('111-11-0100'),
       ('222-22-0120'),
       ('333-33-0140'),
       ('444-44-0160');

select *
from ssn;

explain
select *
from ssn;

CREATE OR REPLACE FUNCTION FN_FIX_SSN(TEXT) RETURNS text AS
$$
BEGIN
    return substring($1, 8) || replace(substring($1, 1, 7), '-', '');

end ;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT ssn, FN_FIX_SSN(ssn)
from ssn;

drop function fn_ssn_compare cascade;

CREATE OR REPLACE FUNCTION fn_ssn_compare(text,text) returns int as
$$
    BEGIN
        if FN_FIX_SSN($1) < FN_FIX_SSN($2) then return -1;
        elseif FN_FIX_SSN($1) > FN_FIX_SSN($2) then return 1;
        else
            return 0;
        end if;

    end;
$$ language plpgsql immutable;

CREATE OPERATOR CLASS op_class_ssn_ops1
FOR TYPE text USING btree
as
        OPERATOR 1 <,
        OPERATOR 2 <=,
        OPERATOR 3 =,
        OPERATOR 4 >=,
        operator 5 >,
        function 1 fn_ssn_compare(text,text);

CREATE INDEX idx_ssn on ssn(ssn op_class_ssn_ops1);

explain
select *
from ssn where ssn.ssn = '222-22-0120';

set enable_seqscan = 'off';
show enable_seqscan;

```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dev117uday.gitbook.io/databases/sql/indexing/custom-indexes.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.