Databases
StackOverflow2013
Postsdbo
POIs this postgres function cost efficient or still have to clean
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs this postgres function cost efficient or still have to clean
    primarykey
    data
    text
    <p>There are two tables in postgres db. english_all and english_glob</p> <p>First table contains words like international,confidential,booting,cooler ...etc</p> <p>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</p> <pre><code>I In Int Inte Inter .. b bo boo boot .. c co coo cool etc.. </code></pre> <p>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.</p> <p>Postgres function for_loop_test is taking two parameter min and max based on that I am selecting words from english_all table.</p> <p>function code is like</p> <pre><code>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; </code></pre> <p>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.</p> <p><strong>edit</strong> Let assume english_all table have words like </p> <ul> <li>footer,settle,question,overflow,database,kingdom</li> </ul> <p>If inMinLength = 5 and inmaxLength=7 then in the outer loop</p> <ul> <li>footer,settle,kingdom</li> </ul> <p>will be selected. For above 3 words inner two loop will apply to get words like</p> <ul> <li>f,fo,foo,<strong>foot</strong>,foote,<strong>footer</strong>,s,se,<strong>set</strong>,sett,settl .... etc.</li> </ul> <p>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.</p> <p>edit2: This is a complete code </p> <pre><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_all_text,wlength) values ('ant',char_length('ant')),('forget',char_length('forget')),('forgive',char_length('forgive')); </code></pre> <p>on function call with parameter 3 and 6 fallowing rows should fetched</p> <pre><code>a an ant f fo for forg forge forget </code></pre> <p>next is insert to another table based on above row</p> <pre><code>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), </code></pre> <p>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)</p> <pre><code>forgi forgiv forgive </code></pre> <p><a href="http://img153.imageshack.us/i/addword.jpg/" rel="nofollow">Screenshot</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkiranking
    1. USkiranking
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs this postgres function cost efficient or still have to clean
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs this postgres function cost efficient or still have to clean
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhen looking at fastest way if you are using looping logic you are not doing the fastest way. Try and re-work it to use straight SQL when at all possible most of the time this is possible and will be the fastest. Edge cases you may need to use some PL.
      singulars
      1. POIs this postgres function cost efficient or still have to clean
      1. USKuberchaun
    2. COthanks for the code. You are inserting `1` into `is_prop` for the words `a`, `an`, `ant`, `for`, `forge` and `forget`. How does `PostgreSQL` know they are proper English words and marks them with a `1`?
      singulars
      1. POIs this postgres function cost efficient or still have to clean
      1. USQuassnoi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload