1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
-- ==============================================================
-- GNUmed database schema change script
--
-- Source database version: v2
-- Target database version: v3
--
-- License: GPL v2 or later
-- Author: Karsten Hilbert
--
-- ==============================================================
-- $Id: dem-create_address.sql,v 1.2 2006-11-14 23:29:57 ncq Exp $
-- $Revision: 1.2 $
-- --------------------------------------------------------------
\set ON_ERROR_STOP 1
-- --------------------------------------------------------------
\unset ON_ERROR_STOP
DROP function dem.create_address(text, text, text, text, text, text);
\set ON_ERROR_STOP 1
create function dem.create_address(text, text, text, text, text, text, text)
returns integer
LANGUAGE 'plpgsql'
AS '
DECLARE
_number ALIAS FOR $1;
_street ALIAS FOR $2;
_postcode ALIAS FOR $3;
_urb ALIAS FOR $4;
_state_code ALIAS FOR $5;
_country_code ALIAS FOR $6;
_subunit alias for $7;
_street_id integer;
_address_id integer;
msg text;
BEGIN
-- create/get street
SELECT INTO _street_id dem.create_street(_street, _postcode, _urb, _state_code, _country_code);
-- create/get and return address
SELECT INTO _address_id a.id from dem.address a WHERE a.number ILIKE _number and a.id_street = _street_id;
IF FOUND THEN
RETURN _address_id;
END IF;
INSERT INTO dem.address (number, id_street, subunit) VALUES ( _number, _street_id, _subunit);
RETURN currval(''dem.address_id_seq'');
END;';
comment on function dem.create_address(text, text, text, text, text, text, text) is
'This function takes as parameters the number of the address,\n
the name of the street, the postal code of the address, the\n
name of the urb, the code of the state, the code of the\n
country and the subunit. If the country or the state do not exist in the\n
database, the function fails.\n
At first, the urb, the street and the address are tried to be\n
retrieved according to the supplied information. If the fields\n
do not match exactly an existing row, a new urb or street is\n
created or a new address is created and returned.';
-- --------------------------------------------------------------
select public.log_script_insertion('$RCSfile: dem-create_address.sql,v $', '$Revision: 1.2 $');
-- ==============================================================
-- $Log: dem-create_address.sql,v $
-- Revision 1.2 2006-11-14 23:29:57 ncq
-- - need to handle subunit, too
--
-- Revision 1.1 2006/11/14 17:32:20 ncq
-- - improve var names so we knows it's state/country *code*
--
-- Revision 1.5 2006/10/24 13:09:45 ncq
-- - What it does duplicates the change log so axe it
--
-- Revision 1.4 2006/09/28 14:39:51 ncq
-- - add comment template
--
-- Revision 1.3 2006/09/18 17:32:53 ncq
-- - make more fool-proof
--
-- Revision 1.2 2006/09/16 21:47:37 ncq
-- - improvements
--
-- Revision 1.1 2006/09/16 14:02:36 ncq
-- - use this as a template for change scripts
--
--
|
