secret
Last active

"Real" world example

  • Download Gist
File.php
PHP
1 2 3 4 5 6 7 8 9
<?php
/** search for Taxon **/
 
$sth = $dbh->prepare("SELECT id, name
FROM taxonomy
WHERE name_normalized like normalize(?) || '%' ");
$sth->bindValue(1, $_POST['name'], PDO::PARAM_STR);
$sth->execute();
$result = $sth->fetch(PDO::FETCH_ASSOC);
fct.sql
SQL
1 2 3 4 5 6 7 8 9 10 11
CREATE OR REPLACE FUNCTION normalize(to_handle varchar) RETURNS varchar STRICT
AS $$
BEGIN
return lower(regexp_replace(handle,'[^[:alnum:]]','', 'g'));
END;
$$ LANGUAGE plpgsql IMMUTABLE;
 
 
CREATE TABLE taxonomy ( id integer, name varchar, name_normalized varchar);
 
/** A trigger that does taxonomy.name_normalized := normalize(name); on insert and update; ***/

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.