trunk/example/useful-functions.sql

-- #########################################################################
-- # Gedafe relies on application logic being implemented in PostgreSQL
-- # here are some code fragments which may help
-- #########################################################################
-- # Tobias Oetiker <oetiker@ee.ethz.ch>
-- #########################################################################

-- Register PLPGSQL
-- ================
-- Without this you will not be able to implement any sensible stored
-- Procedures. Note that you have to adjust the Path to the shared library

create function plpgsql_call_handler() returns opaque
        as '/usr/pack/postgresql-7.0.3-to/lib/plpgsql.so'
        language 'C';

create trusted procedural language 'plpgsql'
        handler plpgsql_call_handler
        lancompiler 'PL/pgSQL';

-- Raise an exception 
-- ==================
-- Sometimes you may want to print an error message to the user.
-- Here is an example how this could be done

CREATE FUNCTION elog(text) RETURNS BOOLEAN 
        AS 'BEGIN RAISE EXCEPTION ''%s'', $1 ; END;' 
        LANGUAGE 'plpgsql';


-- Example use for elog(). This prevents updates to records in the
-- personel table unless they refer to the current user.

CREATE RULE personel_update_test AS 
       ON UPDATE TO personel
       WHERE new.name != old.name OR new.name != current_user
       DO INSTEAD SELECT elog('You can only Update your own Records');


-- Group Membership Test
-- =====================
-- Test if a user is a member of a specific Group

CREATE FUNCTION getgroup(name,int4) RETURNS int4 AS '
     SELECT grolist[$2]
           FROM pg_group
           WHERE groname = $1' LANGUAGE 'sql';

CREATE FUNCTION ingroup(name) RETURNS BOOLEAN AS '
DECLARE
        group ALIAS FOR $1;
        fuid int4;
        uid int4;
        i int4;
BEGIN
  SELECT INTO uid usesysid
         FROM pg_user
         WHERE usename = getpgusername();
  IF NOT FOUND THEN RETURN FALSE; END IF;
  i := 1;
  LOOP  
    SELECT INTO fuid getgroup(group,i);
    IF NOT FOUND THEN RETURN FALSE; END IF;
    IF fuid IS NULL THEN RETURN FALSE; END IF;
    IF fuid = uid THEN RETURN TRUE; END IF;
    i := i+1; 
  END LOOP;
  RETURN FALSE;
END;
' LANGUAGE 'plpgsql';


-- Nice Trim
-- =========
-- Trim a Long Text string in a sensible fashion

DROP FUNCTION nicetrim (text, int4);
CREATE FUNCTION nicetrim (text, int4) RETURNS text AS '
        DECLARE
                str ALIAS FOR $1;
                len ALIAS FOR $2;
        BEGIN
                IF char_length(str) > len THEN
                        RETURN substring(str from 1 for len) || '' [...]'';
                END IF;
                RETURN str;
        END;
' LANGUAGE 'plpgsql';


-- HumanID to ID
-- =============
-- Figure out the id of a record from its hid

CREATE FUNCTION hid2id(NAME) returns int4
       AS 'SELECT personel_id FROM personel WHERE personel_hid = $1 ' 
       LANGUAGE 'sql';


1	dpavlin	1	-- #########################################################################
2			-- # Gedafe relies on application logic being implemented in PostgreSQL
3			-- # here are some code fragments which may help
4			-- #########################################################################
5			-- # Tobias Oetiker <oetiker@ee.ethz.ch>
6			-- #########################################################################
7
8			-- Register PLPGSQL
9			-- ================
10			-- Without this you will not be able to implement any sensible stored
11			-- Procedures. Note that you have to adjust the Path to the shared library
12
13			create function plpgsql_call_handler() returns opaque
14			as '/usr/pack/postgresql-7.0.3-to/lib/plpgsql.so'
15			language 'C';
16
17			create trusted procedural language 'plpgsql'
18			handler plpgsql_call_handler
19			lancompiler 'PL/pgSQL';
20
21			-- Raise an exception
22			-- ==================
23			-- Sometimes you may want to print an error message to the user.
24			-- Here is an example how this could be done
25
26			CREATE FUNCTION elog(text) RETURNS BOOLEAN
27			AS 'BEGIN RAISE EXCEPTION ''%s'', $1 ; END;'
28			LANGUAGE 'plpgsql';
29
30
31			-- Example use for elog(). This prevents updates to records in the
32			-- personel table unless they refer to the current user.
33
34			CREATE RULE personel_update_test AS
35			ON UPDATE TO personel
36			WHERE new.name != old.name OR new.name != current_user
37			DO INSTEAD SELECT elog('You can only Update your own Records');
38
39
40			-- Group Membership Test
41			-- =====================
42			-- Test if a user is a member of a specific Group
43
44			CREATE FUNCTION getgroup(name,int4) RETURNS int4 AS '
45			SELECT grolist[$2]
46			FROM pg_group
47			WHERE groname = $1' LANGUAGE 'sql';
48
49			CREATE FUNCTION ingroup(name) RETURNS BOOLEAN AS '
50			DECLARE
51			group ALIAS FOR $1;
52			fuid int4;
53			uid int4;
54			i int4;
55			BEGIN
56			SELECT INTO uid usesysid
57			FROM pg_user
58			WHERE usename = getpgusername();
59			IF NOT FOUND THEN RETURN FALSE; END IF;
60			i := 1;
61			LOOP
62			SELECT INTO fuid getgroup(group,i);
63			IF NOT FOUND THEN RETURN FALSE; END IF;
64			IF fuid IS NULL THEN RETURN FALSE; END IF;
65			IF fuid = uid THEN RETURN TRUE; END IF;
66			i := i+1;
67			END LOOP;
68			RETURN FALSE;
69			END;
70			' LANGUAGE 'plpgsql';
71
72
73			-- Nice Trim
74			-- =========
75			-- Trim a Long Text string in a sensible fashion
76
77			DROP FUNCTION nicetrim (text, int4);
78			CREATE FUNCTION nicetrim (text, int4) RETURNS text AS '
79			DECLARE
80			str ALIAS FOR $1;
81			len ALIAS FOR $2;
82			BEGIN
83			IF char_length(str) > len THEN
84			RETURN substring(str from 1 for len) \|\| '' [...]'';
85			END IF;
86			RETURN str;
87			END;
88			' LANGUAGE 'plpgsql';
89
90
91			-- HumanID to ID
92			-- =============
93			-- Figure out the id of a record from its hid
94
95			CREATE FUNCTION hid2id(NAME) returns int4
96			AS 'SELECT personel_id FROM personel WHERE personel_hid = $1 '
97			LANGUAGE 'sql';
98
99
100