/[clipping]/db/clipping_proc.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 /db/clipping_proc.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.1.1.1 - (show annotations) (vendor branch)
Fri Jan 16 22:46:52 2004 UTC (20 years, 2 months ago) by dpavlin
Branch: MAIN, phperl
CVS Tags: r20040116, HEAD
Changes since 1.1: +0 -0 lines
initial import of downloaded version

1 --
2 -- Project: Clipping
3 -- Author: Nelson Ferraz <nferraz@phperl.com>
4 -- Date: 2003-08-14
5 --
6 -- More information on triggers:
7 -- http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html
8 --
9
10
11 --
12 -- STORED PROCEDURE: INSERT CUSTOMER
13 --
14
15 CREATE FUNCTION sp_ins_c_customer (
16 varchar /* c_customer_name */,
17 varchar /* c_customer_phone */,
18 varchar /* c_customer_email */,
19 text /* c_customer_notes */
20 )
21 RETURNS BOOLEAN
22 AS '
23 DECLARE
24 p_c_customer_name ALIAS FOR $1;
25 p_c_customer_phone ALIAS FOR $2;
26 p_c_customer_email ALIAS FOR $3;
27 p_c_customer_notes ALIAS FOR $4;
28
29 BEGIN
30
31 INSERT INTO c_customer (
32 c_customer_name,
33 c_customer_phone,
34 c_customer_email,
35 c_customer_notes
36 ) VALUES (
37 p_c_customer_name,
38 p_c_customer_phone,
39 p_c_customer_email,
40 p_c_customer_notes
41 );
42
43 RETURN TRUE;
44
45 END;
46 ' LANGUAGE 'plpgsql';
47
48
49 --
50 -- STORED PROCEDURE: UPDATE CUSTOMER
51 --
52
53 CREATE FUNCTION sp_upd_c_customer (
54 int /* p_id */,
55 varchar /* c_customer_name */,
56 varchar /* c_customer_phone */,
57 varchar /* c_customer_email */,
58 text /* c_customer_notes */
59 )
60 RETURNS BOOLEAN
61 AS '
62 DECLARE
63
64 p_id ALIAS FOR $1;
65 p_c_customer_name ALIAS FOR $2;
66 p_c_customer_phone ALIAS FOR $3;
67 p_c_customer_email ALIAS FOR $4;
68 p_c_customer_notes ALIAS FOR $5;
69
70 BEGIN
71
72 UPDATE c_customer
73 SET
74 c_customer_name = p_c_customer_name,
75 c_customer_phone = p_c_customer_phone,
76 c_customer_email = p_c_customer_email,
77 c_customer_notes = p_c_customer_notes,
78 c_customer_date_updated = NOW()
79 WHERE
80 c_customer_id = p_id;
81
82 RETURN TRUE;
83
84 END;
85 ' LANGUAGE 'plpgsql';
86
87
88 --
89 -- STORED PROCEDURE: DELETE CUSTOMER
90 --
91
92 CREATE FUNCTION sp_del_c_customer (
93 int /* p_id */
94 )
95 RETURNS BOOLEAN
96 AS '
97 DECLARE
98
99 p_id ALIAS FOR $1;
100
101 BEGIN
102
103 DELETE
104 FROM c_customer
105 WHERE c_customer_id = p_id;
106
107 RETURN TRUE;
108
109 END;
110 ' LANGUAGE 'plpgsql';
111
112
113 --
114 -- STORED PROCEDURE: LOG CUSTOMER
115 --
116
117 /*
118 CREATE FUNCTION sp_log_c_customer () RETURNS OPAQUE AS '
119 BEGIN
120
121 INSERT INTO log_c_customer (
122 action,
123 c_customer_name,
124 c_customer_phone,
125 c_customer_email,
126 c_customer_notes
127 ) VALUES (
128 TG_OP, -- insert or update
129 NEW.c_customer_name,
130 NEW.c_customer_phone,
131 NEW.c_customer_email,
132 NEW.c_customer_notes
133 );
134 END;
135 ' LANGUAGE 'plpgsql';
136
137 CREATE TRIGGER trig_c_customer BEFORE INSERT OR UPDATE ON c_customer
138 FOR EACH ROW EXECUTE PROCEDURE sp_log_c_customer();
139 */
140
141
142
143 --
144 -- STORED PROCEDURE: INSERT SOURCE
145 --
146
147 CREATE FUNCTION sp_ins_c_source (
148 varchar /* c_source_description */,
149 text /* c_source_notes */
150 )
151 RETURNS BOOLEAN
152 AS '
153 DECLARE
154 p_c_source_description ALIAS FOR $1;
155 p_c_source_notes ALIAS FOR $2;
156
157 BEGIN
158
159 INSERT INTO c_source (
160 c_source_description,
161 c_source_notes
162 ) VALUES (
163 p_c_source_description,
164 p_c_source_notes
165 );
166
167 RETURN TRUE;
168
169 END;
170 ' LANGUAGE 'plpgsql';
171
172
173 --
174 -- STORED PROCEDURE: UPDATE SOURCE
175 --
176
177 CREATE FUNCTION sp_upd_c_source (
178 int /* p_id */,
179 varchar /* c_source_description */,
180 text /* c_source_notes */
181 )
182 RETURNS BOOLEAN
183 AS '
184 DECLARE
185
186 p_id ALIAS FOR $1;
187 p_c_source_description ALIAS FOR $2;
188 p_c_source_notes ALIAS FOR $3;
189
190 BEGIN
191
192 UPDATE c_source
193 SET
194 c_source_description = p_c_source_description,
195 c_source_notes = p_c_source_notes,
196 c_source_date_updated = NOW()
197 WHERE
198 c_source_id = p_id;
199
200 RETURN TRUE;
201
202 END;
203 ' LANGUAGE 'plpgsql';
204
205
206 --
207 -- STORED PROCEDURE: DELETE SOURCE
208 --
209
210 CREATE FUNCTION sp_del_c_source (
211 int /* p_id */
212 )
213 RETURNS BOOLEAN
214 AS '
215 DECLARE
216
217 p_id ALIAS FOR $1;
218
219 BEGIN
220
221 DELETE
222 FROM c_source
223 WHERE c_source_id = p_id;
224
225 RETURN TRUE;
226
227 END;
228 ' LANGUAGE 'plpgsql';
229
230
231 --
232 -- STORED PROCEDURE: LOG SOURCE
233 --
234
235 /*
236 CREATE FUNCTION sp_log_c_source () RETURNS OPAQUE AS '
237 BEGIN
238
239 INSERT INTO log_c_source (
240 action,
241 c_source_description,
242 c_source_notes
243 ) VALUES (
244 TG_OP, -- insert or update
245 NEW.c_source_description,
246 NEW.c_source_notes
247 );
248 END;
249 ' LANGUAGE 'plpgsql';
250
251 CREATE TRIGGER trig_c_source BEFORE INSERT OR UPDATE ON c_source
252 FOR EACH ROW EXECUTE PROCEDURE sp_log_c_source();
253 */
254
255
256
257 --
258 -- STORED PROCEDURE: INSERT TYPE
259 --
260
261 CREATE FUNCTION sp_ins_c_type (
262 varchar /* c_type_description */,
263 text /* c_type_notes */
264 )
265 RETURNS BOOLEAN
266 AS '
267 DECLARE
268 p_c_type_description ALIAS FOR $1;
269 p_c_type_notes ALIAS FOR $2;
270
271 BEGIN
272
273 INSERT INTO c_type (
274 c_type_description,
275 c_type_notes
276 ) VALUES (
277 p_c_type_description,
278 p_c_type_notes
279 );
280
281 RETURN TRUE;
282
283 END;
284 ' LANGUAGE 'plpgsql';
285
286
287 --
288 -- STORED PROCEDURE: UPDATE TYPE
289 --
290
291 CREATE FUNCTION sp_upd_c_type (
292 int /* p_id */,
293 varchar /* c_type_description */,
294 text /* c_type_notes */
295 )
296 RETURNS BOOLEAN
297 AS '
298 DECLARE
299
300 p_id ALIAS FOR $1;
301 p_c_type_description ALIAS FOR $2;
302 p_c_type_notes ALIAS FOR $3;
303
304 BEGIN
305
306 UPDATE c_type
307 SET
308 c_type_description = p_c_type_description,
309 c_type_notes = p_c_type_notes,
310 c_type_date_updated = NOW()
311 WHERE
312 c_type_id = p_id;
313
314 RETURN TRUE;
315
316 END;
317 ' LANGUAGE 'plpgsql';
318
319
320 --
321 -- STORED PROCEDURE: DELETE TYPE
322 --
323
324 CREATE FUNCTION sp_del_c_type (
325 int /* p_id */
326 )
327 RETURNS BOOLEAN
328 AS '
329 DECLARE
330
331 p_id ALIAS FOR $1;
332
333 BEGIN
334
335 DELETE
336 FROM c_type
337 WHERE c_type_id = p_id;
338
339 RETURN TRUE;
340
341 END;
342 ' LANGUAGE 'plpgsql';
343
344
345 --
346 -- STORED PROCEDURE: LOG TYPE
347 --
348
349 /*
350 CREATE FUNCTION sp_log_c_type () RETURNS OPAQUE AS '
351 BEGIN
352
353 INSERT INTO log_c_type (
354 action,
355 c_type_description,
356 c_type_notes
357 ) VALUES (
358 TG_OP, -- insert or update
359 NEW.c_type_description,
360 NEW.c_type_notes
361 );
362 END;
363 ' LANGUAGE 'plpgsql';
364
365 CREATE TRIGGER trig_c_type BEFORE INSERT OR UPDATE ON c_type
366 FOR EACH ROW EXECUTE PROCEDURE sp_log_c_type();
367 */
368
369
370
371 --
372 -- STORED PROCEDURE: INSERT NEWS
373 --
374
375 CREATE FUNCTION sp_ins_c_news (
376 int /* c_source_fk */,
377 int /* c_customer_fk */,
378 int /* c_type_fk */,
379 varchar /* c_news_section */,
380 varchar /* c_news_page */,
381 varchar /* c_news_title */,
382 text /* c_news_abstract */,
383 text /* c_news_full_text */
384 )
385 RETURNS BOOLEAN
386 AS '
387 DECLARE
388 p_c_source_fk ALIAS FOR $1;
389 p_c_customer_fk ALIAS FOR $2;
390 p_c_type_fk ALIAS FOR $3;
391 p_c_news_section ALIAS FOR $4;
392 p_c_news_page ALIAS FOR $5;
393 p_c_news_title ALIAS FOR $6;
394 p_c_news_abstract ALIAS FOR $7;
395 p_c_news_full_text ALIAS FOR $8;
396
397 BEGIN
398
399 INSERT INTO c_news (
400 c_source_fk,
401 c_customer_fk,
402 c_type_fk,
403 c_news_section,
404 c_news_page,
405 c_news_title,
406 c_news_abstract,
407 c_news_full_text
408 ) VALUES (
409 p_c_source_fk,
410 p_c_customer_fk,
411 p_c_type_fk,
412 p_c_news_section,
413 p_c_news_page,
414 p_c_news_title,
415 p_c_news_abstract,
416 p_c_news_full_text
417 );
418
419 RETURN TRUE;
420
421 END;
422 ' LANGUAGE 'plpgsql';
423
424
425 --
426 -- STORED PROCEDURE: UPDATE NEWS
427 --
428
429 CREATE FUNCTION sp_upd_c_news (
430 int /* p_id */,
431 int /* c_source_fk */,
432 int /* c_customer_fk */,
433 int /* c_type_fk */,
434 varchar /* c_news_section */,
435 varchar /* c_news_page */,
436 varchar /* c_news_title */,
437 text /* c_news_abstract */,
438 text /* c_news_full_text */
439 )
440 RETURNS BOOLEAN
441 AS '
442 DECLARE
443
444 p_id ALIAS FOR $1;
445 p_c_source_fk ALIAS FOR $2;
446 p_c_customer_fk ALIAS FOR $3;
447 p_c_type_fk ALIAS FOR $4;
448 p_c_news_section ALIAS FOR $5;
449 p_c_news_page ALIAS FOR $6;
450 p_c_news_title ALIAS FOR $7;
451 p_c_news_abstract ALIAS FOR $8;
452 p_c_news_full_text ALIAS FOR $9;
453
454 BEGIN
455
456 UPDATE c_news
457 SET
458 c_source_fk = p_c_source_fk,
459 c_customer_fk = p_c_customer_fk,
460 c_type_fk = p_c_type_fk,
461 c_news_section = p_c_news_section,
462 c_news_page = p_c_news_page,
463 c_news_title = p_c_news_title,
464 c_news_abstract = p_c_news_abstract,
465 c_news_full_text = p_c_news_full_text,
466 c_news_date_updated = NOW()
467 WHERE
468 c_news_id = p_id;
469
470 RETURN TRUE;
471
472 END;
473 ' LANGUAGE 'plpgsql';
474
475
476 --
477 -- STORED PROCEDURE: DELETE NEWS
478 --
479
480 CREATE FUNCTION sp_del_c_news (
481 int /* p_id */
482 )
483 RETURNS BOOLEAN
484 AS '
485 DECLARE
486
487 p_id ALIAS FOR $1;
488
489 BEGIN
490
491 DELETE
492 FROM c_news
493 WHERE c_news_id = p_id;
494
495 RETURN TRUE;
496
497 END;
498 ' LANGUAGE 'plpgsql';
499
500
501 --
502 -- STORED PROCEDURE: LOG NEWS
503 --
504
505 /*
506 CREATE FUNCTION sp_log_c_news () RETURNS OPAQUE AS '
507 BEGIN
508
509 INSERT INTO log_c_news (
510 action,
511 c_source_fk,
512 c_customer_fk,
513 c_type_fk,
514 c_news_section,
515 c_news_page,
516 c_news_title,
517 c_news_abstract,
518 c_news_full_text
519 ) VALUES (
520 TG_OP, -- insert or update
521 NEW.c_source_fk,
522 NEW.c_customer_fk,
523 NEW.c_type_fk,
524 NEW.c_news_section,
525 NEW.c_news_page,
526 NEW.c_news_title,
527 NEW.c_news_abstract,
528 NEW.c_news_full_text
529 );
530 END;
531 ' LANGUAGE 'plpgsql';
532
533 CREATE TRIGGER trig_c_news BEFORE INSERT OR UPDATE ON c_news
534 FOR EACH ROW EXECUTE PROCEDURE sp_log_c_news();
535 */
536
537
538
539 -- END --
540
541 --
542 -- This program is free software; you can redistribute it and/or
543 -- modify it under the terms of the GNU General Public License
544 --

  ViewVC Help
Powered by ViewVC 1.1.26