3 INSERT INTO config.upgrade_log (version) VALUES ('0350'); -- Scott McKellar
5 CREATE OR REPLACE FUNCTION action.apply_fieldset(
6 fieldset_id IN INT, -- id from action.fieldset
7 table_name IN TEXT, -- table to be updated
8 pkey_name IN TEXT, -- name of primary key column in that table
9 query IN TEXT -- query constructed by qstore (for query-based
10 -- fieldsets only; otherwise null
25 IF fieldset_id IS NULL THEN
26 RETURN 'Fieldset ID parameter is NULL';
28 IF table_name IS NULL THEN
29 RETURN 'Table name parameter is NULL';
31 IF pkey_name IS NULL THEN
32 RETURN 'Primary key name parameter is NULL';
35 statement := 'UPDATE ' || table_name || ' SET';
39 quote_literal( pkey_value )
48 IF fs_status IS NULL THEN
49 RETURN 'No fieldset found for id = ' || fieldset_id;
50 ELSIF fs_status = 'APPLIED' THEN
51 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
58 FROM action.fieldset_col_val
59 WHERE fieldset = fieldset_id
61 statement := statement || sep || ' ' || cv.col
62 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
67 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
70 -- Add the WHERE clause. This differs according to whether it's a
71 -- single-row fieldset or a query-based fieldset.
73 IF query IS NULL AND fs_pkey_value IS NULL THEN
74 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
75 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
76 fs_query := rtrim( query, ';' );
77 statement := statement || ' WHERE ' || pkey_name || ' IN ( '
79 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
80 statement := statement || ' WHERE ' || pkey_name || ' = '
81 || fs_pkey_value || ';';
82 ELSE -- both are not null
83 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
90 GET DIAGNOSTICS update_count = ROW_COUNT;
92 IF UPDATE_COUNT > 0 THEN
93 status_code := 'APPLIED';
96 status_code := 'ERROR';
97 msg := 'No eligible rows found for fieldset ' || fieldset_id;
99 EXCEPTION WHEN OTHERS THEN
100 status_code := 'ERROR';
101 msg := 'Unable to apply fieldset ' || fieldset_id
105 -- Update fieldset status
107 UPDATE action.fieldset
108 SET status = status_code,
110 WHERE id = fieldset_id;
116 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
118 * Applies a specified fieldset, using a supplied table name and primary
119 * key name. The query parameter should be non-null only for
120 * query-based fieldsets.
122 * Returns NULL if successful, or an error message if not.