Pull to refresh

Comments 23

Зачем надо получать DDL из функции — непонятно. Для этого вполне удобен pgAdmin. Да и вызов внешнего процесса — это нелегко.
Функции urldecode/urlencode и получение доменного имени тоже более уместны на уровне middleware или прочего серверного кода.
Получение DDL полезно для автоматизации некоторых административных задач.
Более уместны на уровне middleware — вы правы. Но это вовсе не означает, что вам никогда не понадобится такой функционал в БД.
Однажды мне понадобилась функция для ранжирования простого поиска.
Функция должна была выдавать позицию слова в строке.
Вот так вышло:

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.
Названия переменных у вас забавные:).

А почему не использовали position(substring in string)?
Забавные?) Вы про needle и haystack? По-моему, так удобнее.

А зачем писал, вот пример:
position( 'dog' IN 'i am a dog' ) = 8
position( 'dog' IN 'i super megadrive dog' ) = 19

А мне нужно было, чтобы такие позиции слова 'dog' расценивались одинаково.
А разбить строки по \s? Или даже просто хранить позиции начало слов. И потом просто поэлементно сравнить? Производительность должна быть много выше.
А лишние пробелы? На момент написания этой функции я смог додуматься только до регекспов… Да и не судите строго — это была моя первая функция на plpgsql))
Лишние пробелы — \s+.
Я конечно не особый знаток Перла, но вот например. Для pg подкорректировать, думаю, не проблема.
#!/usr/bin/perl -w
use strict;
use warnings;

my $haystack = 'i super megadrive dog';
my $needle = 'dog';
$needle =~ s/(?=["'`\\])/\\/sg; # замена спец. символов
if ( $haystack =~ /\b($needle)\b/ ) {
my $r = $`; # теперь в $r часть строки ДО совпадения (искомого слова)
my $pos = 0;
$pos++ while $r =~ /\b?\s+\b?/g; # подсчет групп пробелов
print $pos."\n";
}
else {
print "Not found\n";
}
По большей части стараюсь использоваться эту БД в работе, открытость привлекает, ну и мощность.
Правда примеров мало, но заметку Вам спасибо, в копилку.
ф-ции описанные:
from_unixtime
unix_timestamp
from_days
to_days
ifnull
date_format
Чтобы завелись некоторые модули drupal нужно повторить эти функции с точностью до названия?
да, это делалось именно для друпала :)
не помню, с каким модулем боролся
был код заточенный под mysql и пришлось создавать ф-ции обёртки, чтобы имена были, как в mysql
Очень полезная функция для оптового назначения прав на таблицы. В мускуле есть встроенный аналог, а здесь приходится исхитряться, особенно если у вас много схем.

-- 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.
Расстояние Левенштейна. Алгоритм не оптимальный, но зато работает с базой в UTF-8.

-- 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.
можно узнать для чего это в вашем случае используется?
Можно. Я делаю спортивную базу данных и очень часто имена спортсменов приходят в разных вариантах транслитерации, иногда бывают и ошибки. Чтобы эти данные можно было как-то объединить, используется несколько критериев — один из них похожесть написания имени и фамилии.
Сумма массивов:
-- Function: sum_arr(integer[], integer[])

-- DROP FUNCTION sum_arr(integer[], integer[]);

CREATE OR REPLACE FUNCTION sum_arr(aarr1 integer[], aarr2 integer[])
 RETURNS integer[] AS
$BODY$
DECLARE
 aRetu Integer[];
 
BEGIN

 -- Initialize the Return array with first array value.

 FOR i IN array_lower(aArr1, 1)..array_upper( aArr1, 1 ) LOOP
   PERFORM array_append( aRetu , aArr1[i] );
 END LOOP;

 -- Add the second array value to return array

 FOR i IN array_lower(aArr2, 1)..array_upper(aArr2, 1 ) LOOP
   IF i > array_upper(aRetu, 1) THEN
   PERFORM array_append(aRetu , aArr2[i]);    
   ELSE
    aRetu[i] := aRetu[i]+aArr2[i];
   END IF;
 END LOOP;

 RETURN aRetu;
END
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;


* This source code was highlighted with Source Code Highlighter.
CREATE OR REPLACE FUNCTION "public"."inets_int2inet" (bigint) RETURNS inet AS
$body$
DECLARE
  ret text;
  op bigint;
  tmp bigint;
BEGIN
  op := $1;

  tmp := op / 16777216;
  ret := CAST(tmp AS text) || '.';
  op := op % 16777216;

  tmp := op / 65536;
  ret := ret || CAST(tmp AS text) || '.';
  op := op % 65536;

  tmp := op / 256;
  ret := ret || CAST(tmp AS text) || '.';
  op := op % 256;

  ret := ret || CAST(op AS text);

  RETURN inet(ret);
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


IP в целое

CREATE OR REPLACE FUNCTION "public"."inets_inet2int" (inet) RETURNS bigint AS
$body$
DECLARE
  ret bigint;
  len integer;
  i integer;
  op1 inet;
  op2 text;
  ch text;
  part text;
  multy int8 := 16777216;
BEGIN
  op1 := $1;
  op2 := host(network(op1));
  len := length(op2);
  ret := 0;
  part := '';
  FOR i IN 1..len LOOP
    ch = substring(op2 from i for 1);
    EXIT WHEN ch='/';
    if ch='.' THEN
      ret := ret + CAST(part AS int8) * multy;
      multy := multy / 256;
      part := '';
    ELSE
      part := part || ch;
    END IF;
  END LOOP;
  ret := ret + CAST(part AS int8);
  RETURN ret;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Кстати, в прошлом топике я рассказывал про модуль «ip4r» — Быстрое местоположения по ip в postgresql. Модуль предоставляет тип «ip4» (IPv4 адрес), который очень просто преобразуется в BIGINT и обратно:
SELECT ip4('192.168.0.1')::BIGINT;
SELECT ip4(3232235521);
А я в том же топике привел пример более компактной и быстрой функции, которую для меня написал один человек из команды постгреса:

create function inet_aton(inet) returns bigint language sql immutable as $f$ select $1 - inet '0.0.0.0' $f$;
create function inet_ntoa(bigint) returns inet language sql immutable as $f$ select $1 + inet '0.0.0.0' $f$;
Первая из функций теперь встроена в 8.4 и называется unnest()
Only those users with full accounts are able to leave comments. Log in, please.