Edited at

Trim space in PostgreSQL plperl

More than 1 year has passed since last update.


Original

the page below contains typo in the code...

https://wiki.postgresql.org/wiki/Normalize_whitespace

here is the working code...


Trim space in PostgreSQL plperl

CREATE OR REPLACE FUNCTION n_space()

RETURNS trigger
LANGUAGE plperl
AS $function$
my $rs = spi_exec_query(<<" EOT");
SELECT
a.attname as name,
format_type(a.atttypid,a.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,a.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";
$function$
;

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


Trim trailing linefeed/return

CREATE OR REPLACE FUNCTION mrm.n_space()

RETURNS trigger
LANGUAGE plperlu
AS $function$
my $rs = spi_exec_query(<<" EOT");
SELECT
a.attname as name,
format_type(a.atttypid,a.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,a.atttypmod) like 'character%'
EOT

my $rs_rows = $rs->{processed}-1;
foreach my $rn (0 .. $rs_rows){
my $row = $rs->{rows}[$rn]->{name};

if (! defined $_TD->{new}{$row}) {
$_TD->{new}{$row} =~ s/\x0D?\x0A?$//;
return "MODIFY";
} else {
return;
}
}
$function$
;