There are two tables in postgres db. english_all and english_glob

First table contains words like international,confidential,booting,cooler ...etc

I have written the function to get the words from english_all then perform for loop for each word to get word list which are not inserted in anglish_glob table. Word list is like

I
In
Int
Inte
Inter
..
b
bo
boo
boot
..
c
co
coo
cool
etc..

for some reason zwnj(zero-width non-joiner) is added during insertion to english_all table. But in function I am removing that character with regexp_replace.

Postgres function for_loop_test is taking two parameter min and max based on that I am selecting words from english_all table.

function code is like

DECLARE
inMinLength ALIAS FOR $1;
inMaxLength ALIAS FOR $2;
mviews RECORD;
outenglishListRow english_word_list;--custom data type eng_id,english_text
BEGIN
FOR mviews IN SELECT id,english_all_text FROM english_all where wlength between inMinLength and inMaxLength 
ORDER BY english_all_text limit 30 LOOP

FOR i IN 1..char_length(regexp_replace(mviews.english_all_text,'(?)$','')) LOOP
    FOR outenglishListRow IN
        SELECT distinct on (regexp_replace((substring(mviews.english_all_text from 1 for i)),'(?)$','')) mviews.id,
        regexp_replace((substring(mviews.english_all_text from 1 for i)),'(?)$','') where 
        regexp_replace((substring(mviews.english_all_text from 1 for i)),'(?)$','') not 
        in(select english_glob.english_text from english_glob where i=english_glob.wlength)
        order by regexp_replace((substring(mviews.english_all_text from 1 for i)),'(?)$','')
    LOOP
    RETURN NEXT outenglishListRow;
    END LOOP;


END LOOP;
END LOOP;
END;

Once I get the word list I will insert that into another table english_glob. My question is is there any thing I can add to or remove from function to make it more efficient.

edit Let assume english_all table have words like

• footer,settle,question,overflow,database,kingdom

If inMinLength = 5 and inmaxLength=7 then in the outer loop

• footer,settle,kingdom

will be selected. For above 3 words inner two loop will apply to get words like

• f,fo,foo,foot,foote,footer,s,se,set,sett,settl .... etc.

In the final process words which are bold will be entered into english_glob with another parameter like 1 to denote it is a proper word and stored in the another filed of english_glob table. Remaining word will be stored with another parameter 0 because in the next call words which are saved in database should not be fetched again.

edit2: This is a complete code

CREATE TABLE english_all
(
id serial NOT NULL,
english_all_text  text NOT NULL,
wlength integer NOT NULL,
CONSTRAINT english_all PRIMARY KEY (id),
CONSTRAINT english_all_kan_text_uq_id UNIQUE (english_all_text)
)

CREATE TABLE english_glob
(
id serial NOT NULL,
english_text  text NOT NULL,
is_prop integer default 1,
CONSTRAINT english_glob PRIMARY KEY (id),
CONSTRAINT english_glob_kan_text_uq_id UNIQUE (english_text)
)

insert into english_all(english_text) values ('ant'),('forget'),('forgive');

on function call with parameter 3 and 6 fallowing rows should fetched

a
an
ant
f
fo
for
forg
forge
forget

next is insert to another table based on above row

insert into english_glob(english_text,is_prop) 
values 
('a',1),('an',1),
('ant',1),('f',0),
('fo',0),('for',1),
('forg',0),('forge',1),
('forget',1),

on function call next time with parameter 3 and 7 fallowing rows should fetched.(because f,fo,for,forg are all entered in english_glob table)

forgi
forgiv
forgive