-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathat_sys_table_compatible.sql
More file actions
80 lines (65 loc) · 2.85 KB
/
at_sys_table_compatible.sql
File metadata and controls
80 lines (65 loc) · 2.85 KB
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
-- Copyright (c) 2009 Astun Technology
-- Permission is hereby granted, free of charge, to any person obtaining a copy
-- of this software and associated documentation files (the "Software"), to deal
-- in the Software without restriction, including without limitation the rights
-- to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
-- copies of the Software, and to permit persons to whom the Software is
-- furnished to do so, subject to the following conditions:
-- The above copyright notice and this permission notice shall be included in
-- all copies or substantial portions of the Software.
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
-- IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
-- FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
-- AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
-- LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
-- OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
-- THE SOFTWARE.
-- Function: at_sys_table_compatible(text, text)
-- DROP FUNCTION at_sys_table_compatible(text, text);
CREATE OR REPLACE FUNCTION at_sys_table_compatible(_curtable text, _newtable text)
RETURNS integer AS
$BODY$
DECLARE
v_res INTEGER;
v_qry TEXT;
v_cnt INTEGER;
v_ncnt INTEGER;
v_newTbl TEXT;
v_curTbl TEXT;
BEGIN
v_newTbl := '^(' || btrim(_newTable,'"') || ')$';
v_curTbl := '^(' || btrim(_curTable,'"') || ')$';
v_qry := ' SELECT COUNT(*) FROM information_schema.columns WHERE table_name ~ ' ;
EXECUTE v_qry || quote_literal(v_curTbl) INTO v_cnt;
EXECUTE v_qry || quote_literal(v_newTbl) INTO v_ncnt;
v_res := v_cnt - v_ncnt;
IF ( v_res != 0 AND v_cnt != 0 )
THEN
RAISE EXCEPTION ' Number of columns are not equal';
END IF;
v_qry := ' SELECT count(*) FROM
(
SELECT a.attname, a.atttypid, a.atttypmod,
a.attnum FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c
WHERE a.attrelid = c.oid and c.relname ~ ' || quote_literal(v_newTbl) ||
' AND a.attnum > 0 ORDER BY a.attnum
) AS type1 NATURAL JOIN
(
SELECT a.attname, a.atttypid, a.atttypmod,
a.attnum FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c
WHERE a.attrelid = c.oid and c.relname ~ ' || quote_literal(v_curTbl) ||
' AND a.attnum > 0 ORDER BY a.attnum
) AS type2';
EXECUTE v_qry INTO v_res;
IF ( v_res != v_cnt OR v_res != v_ncnt )
THEN
RAISE EXCEPTION ' tables are not comparable';
END IF;
RETURN 0;
EXCEPTION WHEN OTHERS THEN
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION at_sys_table_compatible(text, text) OWNER TO postgres;
COMMENT ON FUNCTION at_sys_table_compatible(text,text) IS 'Reserved - Astun Technology Core Function';