规范空白

库代码片段

规范空格

适用于 PostgreSQL

任何版本

用

SQL

依赖于

无

此 SQL 函数规范字符串中的空格，移除任何前导或尾随空格，并将任何内部空白缩减为每次出现一个空格字符。它在创建域时很有用。

CREATE OR REPLACE FUNCTION normalize_space(TEXT)
RETURNS TEXT
IMMUTABLE
LANGUAGE SQL
AS $$
SELECT regexp_replace(
    trim($1),
    E'\\s+',
    ' ',
    'g'
);
$$;

CREATE OR REPLACE FUNCTION normalize_space(text) 
RETURNS text LANGUAGE plperl AS $$
    $_ = $_[0]; 
    s/(^\s+|\s+$)//g;
    s/\s+/ /og;
    return $_;
$$;

因此，如果您想更新/插入到表中并规范之前该操作的文本，您可以在每次插入之前创建一个触发器，如下所示

 
CREATE TABLE pepito (wordy text, moody text, bar text);

DROP IF EXISTS trigger n_space on pepito;

CREATE TRIGGER n_space BEFORE INSERT OR UPDATE ON pepito FOR EACH ROW EXECUTE PROCEDURE n_space();

此函数触发器查找文本类型的字段，并使用替换表达式修改 NEW 值。

CREATE OR REPLACE FUNCTION n_space() 
RETURNS trigger
VOLATILE
LANGUAGE plperl
AS $$
	my $rs = spi_exec_query(<<"    EOT");
    SELECT
        a.attname as name,
        format_type(a.atttypid,pg_attribute.atttypmod) 
    FROM
        pg_class AS c
    JOIN
        pg_attribute AS a
        ON (
            a.attrelid = c.oid
        AND c.oid = '$_TD->{table_name}'::regclass 
        AND a.attstorage <> 'p'
        AND format_type(a.atttypid,pg_attribute.atttypmod) = 'text'
    EOT
        
	my $rs_rows = $rs->{processed}-1;
	foreach my $rn (0 .. $rs_rows){
		my $row = $rs->{rows}[$rn]->{name};
		$_TD->{new}{$row} =~ s/(^\s+|\s+$)//g;
		$_TD->{new}{$row} =~ s/\s+/ /g;		
	}	
	return "MODIFY";
$$;

TRUNCATE TABLE pepito;
INSERT INTO pepito
VALUES
    ('asd asd AS     asd asf      ',' asd  4t45gr g er    ergt',' asd sa'),
    ('asd asd AS     asdbhfg',' asd  4t45gr g er    ergt',' asd sa'),
    ('    asd asd AS     asd asf      ',' asd  4t45gr g er    ergt',' asd sa');
SELECT * FROM pepito;