Мелочи, облегчающие жизнь

CREATE OR REPLACE FUNCTION wordPos ( varchar, varchar ) RETURNS integer AS $$
DECLARE
 int_haystack ALIAS FOR $1;
 int_needle ALIAS FOR $2;
 haystack varchar;
 needle varchar;
 pos integer;
BEGIN
  pos = 0;
  needle   = lower( int_needle );
  haystack = lower( int_haystack );
  IF strpos(haystack, needle) > 0 THEN
      haystack = lower( ( regexp_replace(haystack, '[[:space:]]+', ' ', 'g' ) ) );
      haystack = regexp_replace( haystack, '^[[:space:]]+', '', 'g');      

      pos = length( regexp_replace( substr( haystack, 1, POSITION( needle IN haystack ) - 1 ), '[[:space:]]+',' ', 'g' ))
        - length( regexp_replace( substr( haystack, 1, POSITION( needle IN haystack ) - 1 ), '[[:space:]]+','', 'g' ));
      pos = pos + 1;
      
  END IF;
  RETURN pos; 
END;
$$ LANGUAGE plpgsql;

* This source code was highlighted with Source Code Highlighter.

-- Function: pg_grant(text, text, text, text)

-- DROP FUNCTION pg_grant(text, text, text, text);

CREATE OR REPLACE FUNCTION pg_grant(user_ text, action_ text, table_ text, schema_ text)
 RETURNS integer AS
$BODY$
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in ('r','v','S') AND
nspname = $4 AND
relname LIKE $3
LOOP
EXECUTE 'GRANT ' || $2 || ' ON ' || $4 || '.' || obj.relname || ' TO ' || $1;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;

-- Использование:

SELECT pg_grant('user1', 'SELECT', '%', 'schema1');

* This source code was highlighted with Source Code Highlighter.

-- Function: plpgsql_edit_distance(text, text)

-- DROP FUNCTION plpgsql_edit_distance(text, text);

CREATE OR REPLACE FUNCTION plpgsql_edit_distance(stra text, strb text)
 RETURNS integer AS
$BODY$
declare
  rows integer;
  cols integer;
begin
  rows := length(stra);
  cols := length(strb);

  IF rows = 0 THEN
    return cols;
  END IF;
  IF cols = 0 THEN
  return rows;
  END IF;

  declare
  row_u integer[];
  row_l integer[];
  diagonal integer;
  upper integer;
  left integer;
  begin
  FOR i in 0..cols LOOP
    row_u[i] := i;
  END LOOP;

  FOR i IN 1..rows LOOP
    row_l[0] := i;
    FOR j IN 1..cols LOOP
      IF substring (stra, i, 1) = substring (strb, j, 1) THEN
      diagonal := row_u[j-1];
    else
      diagonal := row_u[j-1] + 1;
    END IF;
    upper := row_u[j] + 1;
    left := row_l[j-1] + 1;
    row_l[j] := int4smaller(int4smaller(diagonal, upper), left);
    END LOOP;
      row_u := row_l;
  END LOOP;
  return row_l[cols];
  end;
end
$BODY$
 LANGUAGE 'plpgsql' IMMUTABLE STRICT
 COST 100;


* This source code was highlighted with Source Code Highlighter.