Created
April 12, 2012 20:05
-
-
Save varnie/2370615 to your computer and use it in GitHub Desktop.
Smart text trimming procedure in PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION th.prc_th_rate_titles_create() | |
RETURNS integer AS | |
$BODY$ | |
DECLARE | |
p_title VARCHAR := ''; | |
p_sql VARCHAR := ''; | |
p_item RECORD; | |
total_rows INTEGER := 0; | |
BEGIN | |
p_sql := 'SELECT id, comment, title FROM th.th_rates'; | |
FOR p_item IN EXECUTE p_sql | |
LOOP | |
IF char_length(p_item.title) = 0 THEN | |
IF char_length(p_item.comment) > 60 THEN | |
p_title = substring(p_item.comment for 61); | |
IF p_title !~ E'\\W$' THEN | |
p_title = regexp_replace(p_title, E'\\M(\\W)+\\s*\\S*$', ''); | |
END IF; | |
p_title = regexp_replace(p_title, E'\\M(\\s*(\\W|за|до|для|под|из|от|то|к|у|о|но|от|по|на|не|с|а|и|в|\\d)+)*\\s*$', '', 'i'); | |
IF char_length(p_title) > 60 THEN | |
p_title = substring(p_title for 60); | |
END IF; | |
ELSE | |
p_title = regexp_replace(p_item.comment, E'\\M(\\W)+\\s*\\S*$', ''); | |
END IF; | |
IF char_length(p_item.comment) != char_length(p_title) THEN | |
p_title = p_title || '...'; | |
END IF; | |
UPDATE th.th_rates SET | |
title = p_title | |
WHERE id = p_item.id; | |
total_rows := total_rows + 1; | |
END IF; | |
END LOOP; | |
RETURN total_rows; | |
END; | |
$BODY$ | |
LANGUAGE plpgsql VOLATILE | |
COST 100; | |
ALTER FUNCTION th.prc_th_rate_titles_create() | |
OWNER TO developer; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment