-
Notifications
You must be signed in to change notification settings - Fork 0
/
at_sys_get_columnlist.sql
61 lines (49 loc) · 2.26 KB
/
at_sys_get_columnlist.sql
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
-- 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_get_columnlist(text)
-- DROP FUNCTION at_sys_get_columnlist(text);
CREATE OR REPLACE FUNCTION at_sys_get_columnlist(_tablename text)
RETURNS text AS
$BODY$
DECLARE
v_qry TEXT;
tablecols RECORD;
v_colCSList TEXT;
v_tableName TEXT;
BEGIN
v_colCSList = '';
v_tableName := '^(' || btrim(_tableName,'"') || ')$';
v_qry :='SELECT a.attname ' ||
'FROM pg_catalog.pg_attribute a ' ||
'WHERE a.attnum > 0 ' ||
'AND NOT a.attisdropped ' ||
'AND a.attrelid = ' ||
'( ' ||
'SELECT c.oid ' ||
'FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace ' ||
'WHERE c.relname = ''' || _tablename || ''' AND pg_catalog.pg_table_is_visible(c.oid) )' ;
FOR tablecols IN EXECUTE v_qry LOOP
v_colCSList := v_colCSList || '"' || tablecols.attname || '",';
END LOOP;
-- Strip off last comma as don't need it.
v_colCSList = trim(trailing ',' from v_colCSList);
RETURN v_colCSList;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION at_sys_get_columnlist(text) OWNER TO postgres;
COMMENT ON FUNCTION at_sys_get_columnlist(text) IS 'Reserved - Astun Technology Core Function';