/[gedafe]/trunk/example/useful-functions.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /trunk/example/useful-functions.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1 - (show annotations)
Mon Feb 14 18:52:26 2005 UTC (19 years, 1 month ago) by dpavlin
File size: 2929 byte(s)
import of Gedafe 1.2.2

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

  ViewVC Help
Powered by ViewVC 1.1.26