There are only simple things. I'm looking forward to see your comments and hints.
Feedback are welcome!
Das sind ein paar einfache Anwendungen, ich würde mich über Hinweise und Anmerkungen freuen.
Reverse String
--
-- rev(varchar) returns the reverse string similar the command-line tool rev
--
create or replace function rev(varchar) returns varchar as $$
declare
_temp varchar;
_count int;
begin
_temp := '';
for _count in reverse length($1)..1 loop
_temp := _temp || substring($1 from _count for 1);
end loop;
return _temp;
end;
$$ language plpgsql immutable;
Eastersunday
--
-- Easter calculater, it returns easter sunday (Ostersonntag)
-- for easter friday subtract 2 days and so on.
--
-- Ascension (Himmelfahrt) is 39 days after
-- Whitsun sunday (Pfingssonnstag) is 49 days after easter sunday
-- Corpus Christi (Fronleichnam) 60 days after
--
-- Thanks to http://www.th-o.de/kalender.htm, where i found the formula
--
create or replace function eastersunday(year integer) returns date as $$
declare
a integer;
b int;
c int;
d int;
e int;
f int;
g int;
h int;
i int;
k int;
l int;
m int;
n int;
p int;
begin
a := $1 % 19;
b := $1 / 100;
c := $1 % 100;
d := b / 4;
e := b % 4;
f := (b + 8) / 25;
g := (b - f + 1) / 3;
h := (19 * a + b - d - g + 15) % 30;
i := c / 4;
k := c % 4;
l := (32 + 2 * e + 2 * i - h - k) % 7;
m := (a + 11 * h + 22 * l) / 451;
n := (h + l - 7 * m + 114) / 31;
p := (h + l - 7 * m + 114) % 31;
return to_date($1 || '/' || n || '/' || p+1, 'yyyy/mm/dd');
end;
$$ language plpgsql immutable;
Multiply-Aggregate
--
-- select the product as a aggregate function (for a int-column)
--
CREATE FUNCTION multiply_aggregate(int,int) RETURNS int AS '
select $1 * $2;
' language sql IMMUTABLE STRICT;
CREATE AGGREGATE multiply (basetype=int, sfunc=multiply_aggregate, stype=int, initcond=1 )
ASCII-String with a hexadecimal number to int
create or replace function ascii2hex(varchar) returns int as $$
declare _count smallint;
_length smallint;
_i smallint;
_ret int;
_x char;
_f smallint;
_s varchar;
begin
_count = 0;
_ret = 0;
_s = lower($1);
if $1 ~* '^[0-9a-f]*$' then else
raise exception 'wrong argument: % is not a hex', $1;
end if;
select into _length length($1);
for _i in 0.._length-1 loop
_x = substring(_s,_length - _i, 1);
_f = ascii(_x)-48;
if _f > 9 then
_f = _f - 39;
end if;
_ret = _ret + _f * 16^_i;
end loop;
return _ret;
end;
$$ language plpgsql immutable;
Drop table if table exists
create or replace function drop_if_exists (varchar) returns bool as $$
declare
c smallint;
begin
select into c count(*) from information_schema.tables
where table_name = $1 and table_type = 'BASE TABLE' ;
if c = 1 then
execute 'drop table ' || $1 ||';';
return true;
else
return false;
end if;
end;
$$ language plpgsql;
--
-- posted by David Fetter
--
CREATE OR REPLACE FUNCTION drop_table(TEXT)
RETURNS VOID
STRICT
LANGUAGE plpgsql
AS $$
BEGIN
BEGIN
EXECUTE 'DROP TABLE ' || $1;
EXCEPTION WHEN UNDEFINED_TABLE THEN
/* do nothing */
RETURN;
END;
RETURN;
END;
$$;
A Sequence with a gap
--
-- someone ask for a sequence with a gap between m and n
--
-- create a function, for a after-trigger
create or replace function _foo_seq() returns trigger as $$
begin
-- we need a gap between 5 and 10
if currval('seq_foo') between 4 and 10 then
perform setval('seq_foo', 10);
end if;
return NULL;
end;
$$ language plpgsql;
create sequence seq_foo;
create table foo (id int default nextval('seq_foo'));
-- now create a after - trigger on insert
create trigger foo_trigger after insert on foo for each row execute procedure _foo_seq();
-- now insert some data
insert into foo values (default);
insert into foo values (default);
insert into foo values (default);
insert into foo values (default);
insert into foo values (default);
insert into foo values (default);
insert into foo values (default);
-- now we expect a gap between 5 and 10 (inclusive)
select * from foo;
id
----
1
2
3
4
11
12
13
(7 rows)
Other useful tools
Tagged Types
Too Cool for Internet Explorer
