schema/spacewalk/postgres/class/evr_t.sql | 2
schema/spacewalk/postgres/packages/rhn_bel.pks | 6
schema/spacewalk/postgres/packages/rhn_channel.pkb | 4
schema/spacewalk/postgres/packages/rhn_config.pkb | 8
schema/spacewalk/postgres/packages/rhn_config.pks | 11 -
schema/spacewalk/postgres/packages/rhn_date_manip.pks | 6
schema/spacewalk/postgres/packages/rhn_exception.pkb | 69 ++-----
schema/spacewalk/postgres/packages/rhn_exception.pks | 4
schema/spacewalk/postgres/packages/rhn_server.pkb | 4
schema/spacewalk/postgres/packages/rhn_user.pkb | 2
schema/spacewalk/postgres/packages/rpm.pkb | 91 +++-------
schema/spacewalk/postgres/packages/rpm.pks | 25 --
schema/spacewalk/postgres/procs/create_new_org.sql | 2
schema/spacewalk/postgres/procs/create_new_user.sql | 4
schema/spacewalk/postgres/procs/delete_server.sql | 4
schema/spacewalk/postgres/procs/does_user_have_role.sql | 2
schema/spacewalk/postgres/procs/lookup_arch_type.sql | 4
schema/spacewalk/postgres/procs/lookup_channel_arch.sql | 2
schema/spacewalk/postgres/procs/lookup_config_info.sql | 2
schema/spacewalk/postgres/procs/lookup_first_matching_cf.sql | 6
schema/spacewalk/postgres/procs/lookup_package_nevra.sql | 6
schema/spacewalk/postgres/procs/lookup_source_name.sql | 4
schema/spacewalk/postgres/procs/new_user_postop.sql | 41 ----
schema/spacewalk/postgres/procs/rhn_prepare_install.sql | 2
schema/spacewalk/postgres/procs/sequence_currval.sql | 6
schema/spacewalk/postgres/procs/sequence_nextval.sql | 6
schema/spacewalk/postgres/procs/set_ks_session_history_message.sql | 2
schema/spacewalk/postgres/triggers/web_contact.sql | 28 ++-
28 files changed, 129 insertions(+), 224 deletions(-)
New commits:
commit d2f16725f65bddae85cd4782cd82e0c84c0a776d
Author: Tom Lane <tgl(a)redhat.com>
Date: Wed Apr 29 22:04:28 2009 -0400
First round of code fixes from manual testing of Postgres functions.
Mostly unconverted nextval syntax, a few null-vs-empty-string
confusions, places where Postgres is pickier about datatype matches,
etc etc.
diff --git a/schema/spacewalk/postgres/class/evr_t.sql
b/schema/spacewalk/postgres/class/evr_t.sql
index 10aaf42..7f4f5cc 100644
--- a/schema/spacewalk/postgres/class/evr_t.sql
+++ b/schema/spacewalk/postgres/class/evr_t.sql
@@ -128,7 +128,7 @@ default for type evr_t using btree as
create or replace function evr_t_as_vre( a evr_t ) returns varchar as $$
begin
- return a.version || '-' || a.release || ':' || a.epoch;
+ return a.version || '-' || a.release || ':' || coalesce(a.epoch,
'');
end;
$$ language plpgsql immutable strict;
diff --git a/schema/spacewalk/postgres/packages/rhn_channel.pkb
b/schema/spacewalk/postgres/packages/rhn_channel.pkb
index 8e70432..c8b7331 100644
--- a/schema/spacewalk/postgres/packages/rhn_channel.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_channel.pkb
@@ -173,7 +173,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
END IF;
insert into rhnServerHistory (id,server_id,summary,details) (
- select rhn_event_id_seq.nextval,
+ select nextval('rhn_event_id_seq'),
server_id_in,
'subscribed to channel ' || SUBSTR(c.label, 0, 106),
c.label
@@ -608,7 +608,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
if deleting_server = 0 then
insert into rhnServerHistory (id,server_id,summary,details) (
- select rhn_event_id_seq.nextval,
+ select nextval('rhn_event_id_seq'),
server_id_in,
'unsubscribed from channel ' || SUBSTR(c.label, 0, 106),
c.label
diff --git a/schema/spacewalk/postgres/packages/rhn_config.pkb
b/schema/spacewalk/postgres/packages/rhn_config.pkb
index 73ace89..6621d8d 100644
--- a/schema/spacewalk/postgres/packages/rhn_config.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_config.pkb
@@ -210,9 +210,8 @@ $$ LANGUAGE 'plpgsql';
declare
retval numeric;
begin
- select nextval (rhn_conffile_id_seq)
- into retval
- from dual;
+ select nextval('rhn_conffile_id_seq')
+ into retval;
insert into rhnConfigFile(id, config_channel_id, config_file_name_id,
state_id
@@ -266,8 +265,7 @@ declare
retval numeric;
begin
select nextval ('rhn_confchan_id_seq')
- into retval
- from dual;
+ into retval;
insert into rhnConfigChannel(id, org_id, confchan_type_id,
name, label, description
diff --git a/schema/spacewalk/postgres/packages/rhn_exception.pkb
b/schema/spacewalk/postgres/packages/rhn_exception.pkb
index c9acb2f..6356188 100644
--- a/schema/spacewalk/postgres/packages/rhn_exception.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_exception.pkb
@@ -7,78 +7,53 @@ update pg_settings set setting = 'rhn_exception,' || setting
where name = 'searc
AS
$$
DECLARE
- exception_details CURSOR (exception_label_in VARCHAR) FOR
- SELECT id, label, message
+ exc RECORD;
+ BEGIN
+
+ SELECT id, label, message INTO exc
FROM rhnException
WHERE label = exception_label_in;
- exc_details_rec RECORD;
-
- return_string VARCHAR(2000);
- BEGIN
- OPEN exception_details(exception_label_in);
- LOOP
- FETCH exception_details into exc_details_rec;
- EXIT WHEN NOT FOUND;
- exception_id_out := exc_details_rec.id;
- exception_message_out := '(' || exc_details_rec.label || ')' || ' -
' || exc_details_rec.message;
-
- END LOOP;
-
-
- IF exception_message_out IS NULL
+ IF NOT FOUND
THEN
- RAISE EXCEPTION 'EXCEPTION NOT FOUND';
+ RAISE EXCEPTION 'Unable to lookup exception with label (%)',
+ exception_label_in;
END IF;
+ exception_id_out := exc.id;
+ exception_message_out := '(' || exc.label || ') - ' ||
exc.message;
END;
$$
LANGUAGE PLPGSQL;
-
-
CREATE OR REPLACE FUNCTION raise_exception(exception_label_in IN VARCHAR) RETURNS
VOID
AS
$$
DECLARE
-
- exception_id NUMERIC;
- exception_message VARCHAR(2000);
exc_rec RECORD;
-
BEGIN
- exc_rec := lookup_exception(exception_label_in);
-
- RAISE EXCEPTION '% : % ',exc_rec.exception_id_out,exception_message_out
;--(exception_id, exception_message);
-
- if no_data_found then
- RAISE EXCEPTION '-20001 : Unable to lookup exception with label
(%)',exception_label_in;
- end if;
+ exc_rec := rhn_exception.lookup_exception(exception_label_in);
+ RAISE EXCEPTION '% : %',
+ exc_rec.exception_id_out,
+ exc_rec.exception_message_out;
END;
$$
LANGUAGE PLPGSQL;
- create or replace function raise_exception_val(exception_label_in in varchar,val_in
in numeric) returns void
+ create or replace function raise_exception_val(exception_label_in in varchar, val_in
in numeric) returns void
as
$$
- declare
- exception_id NUMERIC;
- exception_message VARCHAR(2000);
-
- exc_details RECORD;
- begin
- --exc_details := lookup_exception(exception_label_in, exception_id,
exception_message);
- exc_details := lookup_exception(exception_label_in);
-
+ DECLARE
+ exc_rec RECORD;
+ BEGIN
+ exc_rec := rhn_exception.lookup_exception(exception_label_in);
- RAISE EXCEPTION '% %
(%)',exc_details.exception_id_out,exc_details.exception_message_out,val_in;
- --RAISE_APPLICATION_ERROR(exception_id, exception_message || ' (' ||
val_in || ')');
- if no_data_found then
- RAISE EXCEPTION '-20001, Unable to lookup exception with label
(%)',exception_label_in;
- --RAISE_APPLICATION_ERROR(-20001, 'Unable to lookup exception with label
(' || exception_label_in || ')');
- end if;
+ RAISE EXCEPTION '% : % (%)',
+ exc_rec.exception_id_out,
+ exc_rec.exception_message_out,
+ val_in;
end;
$$
language plpgsql;
diff --git a/schema/spacewalk/postgres/packages/rhn_server.pkb
b/schema/spacewalk/postgres/packages/rhn_server.pkb
index 05d8997..0534010 100644
--- a/schema/spacewalk/postgres/packages/rhn_server.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_server.pkb
@@ -258,7 +258,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
and cf.id = lookup_first_matching_cf(scc.server_id, cfn.path);
locked integer;
begin
- select rhn_snapshot_id_seq.nextval into snapshot_id_v from dual;
+ select nextval('rhn_snapshot_id_seq') into snapshot_id_v;
insert into rhnSnapshot (id, org_id, server_id, reason) (
select snapshot_id_v,
@@ -282,7 +282,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
while true loop
begin
insert into rhnPackageNEVRA (id, name_id, evr_id, package_arch_id)
- select rhn_pkgnevra_id_seq.nextval, sp.name_id, sp.evr_id,
sp.package_arch_id
+ select nextval('rhn_pkgnevra_id_seq'), sp.name_id, sp.evr_id,
sp.package_arch_id
from rhnServerPackage sp
where sp.server_id = server_id_in
and not exists
diff --git a/schema/spacewalk/postgres/packages/rhn_user.pkb
b/schema/spacewalk/postgres/packages/rhn_user.pkb
index ce0e70f..25058ff 100644
--- a/schema/spacewalk/postgres/packages/rhn_user.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_user.pkb
@@ -150,7 +150,7 @@ create or replace
id, address,
user_id, state_id
) (
- select rhn_eaddress_id_seq.nextval, addr.address,
+ select nextval('rhn_eaddress_id_seq'), addr.address,
user_id_in, eas.id
from rhnEmailAddressState eas
where eas.label = 'unverified'
diff --git a/schema/spacewalk/postgres/packages/rpm.pkb
b/schema/spacewalk/postgres/packages/rpm.pkb
index 9aa24c9..78331cf 100644
--- a/schema/spacewalk/postgres/packages/rpm.pkb
+++ b/schema/spacewalk/postgres/packages/rpm.pkb
@@ -5,8 +5,6 @@ update pg_settings set setting = 'rpm,' || setting where name =
'search_path';
create or replace function isdigit(ch CHAR)
RETURNS BOOLEAN as $$
- declare
- vercmp_counter NUMERIC := 0;
BEGIN
if ascii(ch) between ascii('0') and ascii('9')
then
@@ -16,7 +14,6 @@ create or replace function isdigit(ch CHAR)
END ;
$$ language 'plpgsql';
-
create or replace FUNCTION isalpha(ch CHAR)
RETURNS BOOLEAN as $$
@@ -46,23 +43,21 @@ $$ language 'plpgsql';
create or replace FUNCTION rpmstrcmp (string1 IN VARCHAR, string2 IN VARCHAR)
- RETURNS NUMERIC as $$
+ RETURNS INTEGER as $$
declare
- digits CHAR(10) := '0123456789';
- lc_alpha CHAR(27) := 'abcdefghijklmnopqrstuvwxyz';
- uc_alpha CHAR(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
- alpha CHAR(54) := lc_alpha || uc_alpha;
- str1 VARCHAR(32767) := string1;
- str2 VARCHAR(32767) := string2;
- one VARCHAR(32767);
- two VARCHAR(32767);
+ str1 VARCHAR := string1;
+ str2 VARCHAR := string2;
+ digits VARCHAR(10) := '0123456789';
+ lc_alpha VARCHAR(27) := 'abcdefghijklmnopqrstuvwxyz';
+ uc_alpha VARCHAR(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
+ alpha VARCHAR(54) := lc_alpha || uc_alpha;
+ one VARCHAR;
+ two VARCHAR;
isnum BOOLEAN;
BEGIN
if str1 is NULL or str2 is NULL
then
- --raise VALUE_ERROR;
- RAISE EXCEPTION 'VALUE_ERROR.';
- null;
+ RAISE EXCEPTION 'VALUE_ERROR.';
end if;
if str1 = str2
@@ -73,25 +68,25 @@ $$ language 'plpgsql';
two := str2;
<<segment_loop>>
- while one is not null and two is not null
+ while one <> '' and two <> ''
loop
declare
- segm1 VARCHAR(32767);
- segm2 VARCHAR(32767);
+ segm1 VARCHAR;
+ segm2 VARCHAR;
begin
--DBMS_OUTPUT.PUT_LINE('Params: ' || one || ',' || two);
-- Throw out all non-alphanum characters
- while one is not null and not isalphanum(one)
+ while one <> '' and not rpm.isalphanum(one)
loop
one := substr(one, 2);
end loop;
- while two is not null and not isalphanum(two)
+ while two <> '' and not rpm.isalphanum(two)
loop
two := substr(two, 2);
end loop;
str1 := one;
str2 := two;
- if str1 is not null and isdigit(str1)
+ if str1 <> '' and rpm.isdigit(str1)
then
str1 := ltrim(str1, digits);
str2 := ltrim(str2, digits);
@@ -101,17 +96,17 @@ $$ language 'plpgsql';
str2 := ltrim(str2, alpha);
isnum := false;
end if;
- if str1 is not null
+ if str1 <> ''
then segm1 := substr(one, 1, length(one) - length(str1));
else segm1 := one;
end if;
- if str2 is not null
+ if str2 <> ''
then segm2 := substr(two, 1, length(two) - length(str2));
else segm2 := two;
end if;
- if segm1 is null then return -1; end if; /* arbitrary */
- if segm2 is null then
+ if segm1 = '' then return -1; end if; /* arbitrary */
+ if segm2 = '' then
if isnum then
return 1;
else
@@ -124,11 +119,11 @@ $$ language 'plpgsql';
segm1 := ltrim(segm1, '0');
segm2 := ltrim(segm2, '0');
- if segm1 is null and segm2 is not null
+ if segm1 = '' and segm2 <> ''
then
return -1;
end if;
- if segm1 is not null and segm2 is null
+ if segm1 <> '' and segm2 = ''
then
return 1;
end if;
@@ -142,7 +137,7 @@ $$ language 'plpgsql';
end;
end loop segment_loop;
- if one is null then return -1; end if;
+ if one = '' then return -1; end if;
return 1;
END ;
$$ language 'plpgsql';
@@ -152,55 +147,31 @@ $$ language 'plpgsql';
create or replace FUNCTION vercmp(
e1 VARCHAR, v1 VARCHAR, r1 VARCHAR,
e2 VARCHAR, v2 VARCHAR, r2 VARCHAR)
- RETURNS NUMERIC as $$
+ RETURNS INTEGER as $$
declare
- rc NUMERIC;
- vercmp_counter NUMeric := 0;
- ep1 NUMERIC;
- ep2 NUMERIC;
+ rc INTEGER;
+ ep1 INTEGER;
+ ep2 INTEGER;
BEGIN
- vercmp_counter := vercmp_counter + 1;
if e1 is null then
ep1 := 0;
else
- ep1 := TO_NUMBER(e1);
+ ep1 := e1::integer;
end if;
if e2 is null then
ep2 := 0;
else
- ep2 := TO_NUMBER(e2);
+ ep2 := e2::integer;
end if;
-- Epochs are non-null; compare them
if ep1 < ep2 then return -1; end if;
if ep1 > ep2 then return 1; end if;
- rc := rpmstrcmp(v1, v2);
+ rc := rpm.rpmstrcmp(v1, v2);
if rc != 0 then return rc; end if;
- return rpmstrcmp(r1, r2);
+ return rpm.rpmstrcmp(r1, r2);
END;
$$ language 'plpgsql';
-
-
- CREATE OR REPLACE FUNCTION vercmpCounter()
- RETURNS NUMERIC AS $$
- declare
- vercmp_counter numeric:=0;
- BEGIN
- return vercmp_counter;
- END ;
- $$ language 'plpgsql';
-
- CREATE OR REPLACE FUNCTION vercmpResetCounter()
- RETURNS NUMeric AS $$
- DECLARE
- result NUMERIC;
- vercmp_counter numeric :=0;
- BEGIN
- result := vercmp_counter;
- vercmp_counter := 0;
- return result;
- END;
-$$ language 'plpgsql';
-- restore the original setting
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rpm')+1) ) where name = 'search_path';
diff --git a/schema/spacewalk/postgres/packages/rpm.pks
b/schema/spacewalk/postgres/packages/rpm.pks
index 5d684eb..46669d2 100644
--- a/schema/spacewalk/postgres/packages/rpm.pks
+++ b/schema/spacewalk/postgres/packages/rpm.pks
@@ -44,7 +44,7 @@ END;
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION rpmstrcmp (string1 IN VARCHAR, string2 IN VARCHAR)
-RETURNS NUMERIC
+RETURNS INTEGER
AS $$
BEGIN
RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
@@ -54,15 +54,7 @@ $$ language 'plpgsql';
CREATE OR REPLACE FUNCTION vercmp(
e1 VARCHAR, v1 VARCHAR, r1 VARCHAR,
e2 VARCHAR, v2 VARCHAR, r2 VARCHAR)
-RETURNS NUMERIC
-AS $$
-BEGIN
- RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
-END;
-$$ language 'plpgsql';
-
-CREATE OR REPLACE FUNCTION vercmpCounter()
-RETURNS NUMERIC
+RETURNS INTEGER
AS $$
BEGIN
RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
@@ -71,4 +63,3 @@ $$ language 'plpgsql';
-- restore the original setting
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rpm')+1) ) where name = 'search_path';
-
diff --git a/schema/spacewalk/postgres/procs/create_new_org.sql
b/schema/spacewalk/postgres/procs/create_new_org.sql
index fbecaf5..20c627f 100644
--- a/schema/spacewalk/postgres/procs/create_new_org.sql
+++ b/schema/spacewalk/postgres/procs/create_new_org.sql
@@ -136,7 +136,7 @@ begin
-- rhnUserServerPerms
insert into rhnServerGroup
( id, name, description, max_members, group_type, org_id )
- select rhn_server_group_id_seq.nextval, sgt.name, sgt.name,
+ select nextval('rhn_server_group_id_seq'), sgt.name, sgt.name,
0, sgt.id, new_org_id
from rhnServerGroupType sgt
where sgt.label = 'sw_mgr_entitled';
diff --git a/schema/spacewalk/postgres/procs/create_new_user.sql
b/schema/spacewalk/postgres/procs/create_new_user.sql
index deb941d..cffac02 100644
--- a/schema/spacewalk/postgres/procs/create_new_user.sql
+++ b/schema/spacewalk/postgres/procs/create_new_user.sql
@@ -80,7 +80,7 @@ begin
first_names_ol, last_name_ol)
values
(user_id_tmp, prefix_in, first_names_in, last_name_in, genqual_in,
- parent_company_in, company_in, title_in, phone_in, fax_in, email_in, pin_in,
+ parent_company_in, company_in, title_in, phone_in, fax_in, email_in, pin_in ::
numeric,
first_names_ol_in, last_name_ol_in);
if address1_in != '.' then
@@ -90,7 +90,7 @@ begin
city, state, zip, country, phone, fax, type,
alt_first_names, alt_last_name)
values
- (web_user_site_info_id_seq.nextval, user_id_tmp, email_in,
+ (nextval('web_user_site_info_id_seq'), user_id_tmp, email_in,
address1_in, address2_in, address3_in,
city_in, state_in, zip_in, country_in, phone_in, fax_in, 'M',
alt_first_names_in, alt_last_name_in);
diff --git a/schema/spacewalk/postgres/procs/delete_server.sql
b/schema/spacewalk/postgres/procs/delete_server.sql
index 1553eda..0da334e 100644
--- a/schema/spacewalk/postgres/procs/delete_server.sql
+++ b/schema/spacewalk/postgres/procs/delete_server.sql
@@ -104,7 +104,7 @@ begin
end loop;
-- filelists
- /* MFA
+ /* TODO
select spfl.file_list_id id bulk collect into filelistsid_c
from rhnServerPreserveFileList spfl
@@ -290,7 +290,7 @@ begin
-- and any probes that were using this Server as a Proxy Scout.
- /*
+ /* TODO
SELECT CP.probe_id bulk collect into probesid_c
FROM rhn_check_probe CP
diff --git a/schema/spacewalk/postgres/procs/does_user_have_role.sql
b/schema/spacewalk/postgres/procs/does_user_have_role.sql
index 8b5201e..69f5dfe 100644
--- a/schema/spacewalk/postgres/procs/does_user_have_role.sql
+++ b/schema/spacewalk/postgres/procs/does_user_have_role.sql
@@ -37,10 +37,10 @@ begin
and ugm.user_group_id = ug.id
and ugt.label = role_in
and ugt.id = ug.group_type;
- return org_admin;
if not found then
return 0;
end if;
+ return org_admin;
end; $$ language plpgsql;
diff --git a/schema/spacewalk/postgres/procs/lookup_arch_type.sql
b/schema/spacewalk/postgres/procs/lookup_arch_type.sql
index bbeff78..673d076 100644
--- a/schema/spacewalk/postgres/procs/lookup_arch_type.sql
+++ b/schema/spacewalk/postgres/procs/lookup_arch_type.sql
@@ -32,6 +32,4 @@ begin
return arch_type_id;
end;
-$$ language plpgsql
-stable;
-
+$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/procs/lookup_channel_arch.sql
b/schema/spacewalk/postgres/procs/lookup_channel_arch.sql
index c0f670c..500e0cd 100644
--- a/schema/spacewalk/postgres/procs/lookup_channel_arch.sql
+++ b/schema/spacewalk/postgres/procs/lookup_channel_arch.sql
@@ -27,8 +27,6 @@ BEGIN
FROM rhnChannelArch
WHERE label = label_in;
- RETURN channel_arch_id;
-
IF NOT FOUND THEN
perform rhn_exception.raise_exception('channel_arch_not_found');
END IF;
diff --git a/schema/spacewalk/postgres/procs/lookup_config_info.sql
b/schema/spacewalk/postgres/procs/lookup_config_info.sql
index aa95e70..605c502 100644
--- a/schema/spacewalk/postgres/procs/lookup_config_info.sql
+++ b/schema/spacewalk/postgres/procs/lookup_config_info.sql
@@ -18,7 +18,7 @@ lookup_config_info
(
username_in in varchar,
groupname_in in varchar,
- filemode_in in varchar
+ filemode_in in numeric
)
returns numeric
as
diff --git a/schema/spacewalk/postgres/procs/lookup_first_matching_cf.sql
b/schema/spacewalk/postgres/procs/lookup_first_matching_cf.sql
index dfc334f..44c965e 100644
--- a/schema/spacewalk/postgres/procs/lookup_first_matching_cf.sql
+++ b/schema/spacewalk/postgres/procs/lookup_first_matching_cf.sql
@@ -43,7 +43,7 @@ begin
-- We don't need to test latest any more,
-- because we're not looking for a revision at
-- all, just the file with the right path
- select cf.id --cfid
+ select cf.id as cfid
from rhnConfigFile cf,
rhnConfigFileName cfn,
rhnConfigChannel cc,
@@ -59,10 +59,6 @@ begin
) a
LIMIT 1;
-
- IF not found then
- null;
- end if;
return retval;
end;
diff --git a/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
b/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
index 0f1eefa..e03ebbb 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
@@ -20,9 +20,9 @@
create or replace function
lookup_package_nevra(
- name_id_in in varchar,
- evr_id_in in varchar,
- package_arch_id_in in varchar,
+ name_id_in in numeric,
+ evr_id_in in numeric,
+ package_arch_id_in in numeric,
ignore_null_name in numeric default 0
) returns numeric
AS
diff --git a/schema/spacewalk/postgres/procs/lookup_source_name.sql
b/schema/spacewalk/postgres/procs/lookup_source_name.sql
index 7913c1a..731efae 100644
--- a/schema/spacewalk/postgres/procs/lookup_source_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_source_name.sql
@@ -29,8 +29,8 @@ BEGIN
where name = name_in;
IF NOT FOUND THEN
- insert into rhnSourceRPM(id, name) values (nextval('(rhn_sourcerpm_id_seq'),
name_in);
- source_id := currval('(rhn_sourcerpm_id_seq');
+ insert into rhnSourceRPM(id, name) values (nextval('rhn_sourcerpm_id_seq'),
name_in);
+ source_id := currval('rhn_sourcerpm_id_seq');
END IF;
RETURN source_id;
diff --git a/schema/spacewalk/postgres/procs/new_user_postop.sql
b/schema/spacewalk/postgres/procs/new_user_postop.sql
index d2ebe17..049df7f 100644
--- a/schema/spacewalk/postgres/procs/new_user_postop.sql
+++ b/schema/spacewalk/postgres/procs/new_user_postop.sql
@@ -25,11 +25,11 @@ declare
org_id_val numeric;
admin_group_val numeric;
begin
- select org_id into org_id_val
+ select org_id into strict org_id_val
from web_contact
where id = user_id_in;
- select id into admin_group_val
+ select id into strict admin_group_val
from rhnUserGroup
where org_id = org_id_val
and group_type = (
@@ -76,41 +76,4 @@ begin
commit;
end;
-/
-show errors
*/
-
-create or replace function new_user_postop
-(
- user_id_in IN numeric
-)
-returns void
-as $$
-declare
- org_id_val numeric;
- admin_group_val numeric;
-begin
- select org_id into org_id_val
- from web_contact
- where id = user_id_in;
-
- select id into admin_group_val
- from rhnUserGroup
- where org_id = org_id_val
- and group_type = (
- select id
- from rhnUserGroupType
- where label = 'org_admin'
- );
-
- insert into rhnUserGroupMembers
- (user_group_id, user_id)
- values
- (admin_group_val, user_id_in);
-
- insert into rhnUserInfo
- (user_id)
- values
- (user_id_in);
-end;
-$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
b/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
index 9e8f9a3..05e51a6 100644
--- a/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
+++ b/schema/spacewalk/postgres/procs/rhn_prepare_install.sql
@@ -36,7 +36,7 @@ declare
max_execution_time_seconds numeric := 10 * 60;
begin
- select rhn_command_q_inst_recid_seq.nextval
+ select nextval('rhn_command_q_inst_recid_seq')
into command_instance_id;
diff --git a/schema/spacewalk/postgres/procs/sequence_currval.sql
b/schema/spacewalk/postgres/procs/sequence_currval.sql
index b91db43..a5b6348 100644
--- a/schema/spacewalk/postgres/procs/sequence_currval.sql
+++ b/schema/spacewalk/postgres/procs/sequence_currval.sql
@@ -1,5 +1,5 @@
-create or replace function sequence_currval( seq_name regclass ) returns numeric as
+create or replace function
+sequence_currval( seq_name regclass ) returns bigint as
$$
- select currval( $1 )::numeric;
+ select currval($1);
$$ language sql;
-
diff --git a/schema/spacewalk/postgres/procs/sequence_nextval.sql
b/schema/spacewalk/postgres/procs/sequence_nextval.sql
index 4a35434..a4406d6 100644
--- a/schema/spacewalk/postgres/procs/sequence_nextval.sql
+++ b/schema/spacewalk/postgres/procs/sequence_nextval.sql
@@ -1,5 +1,5 @@
-create or replace function sequence_nextval( seq_name regclass ) returns numeric as
+create or replace function
+sequence_nextval( seq_name regclass ) returns bigint as
$$
- select nextval( $1 )::numeric;
+ select nextval($1);
$$ language sql;
-
diff --git a/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
b/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
index 88440db..3535917 100644
--- a/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
+++ b/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
@@ -61,7 +61,7 @@ begin
insert into rhnKickstartSessionHistory (
id, kickstart_session_id, state_id, message
) values (
- rhn_ks_sessionhist_id_seq.nextval,
+ nextval('rhn_ks_sessionhist_id_seq'),
kickstart_session_id_in,
id_states_cursor,
message_in
diff --git a/schema/spacewalk/postgres/triggers/web_contact.sql
b/schema/spacewalk/postgres/triggers/web_contact.sql
index ec1b054..cd4d0e2 100644
--- a/schema/spacewalk/postgres/triggers/web_contact.sql
+++ b/schema/spacewalk/postgres/triggers/web_contact.sql
@@ -15,13 +15,25 @@
--
--
-create or replace function web_contact_mod_trig_fun() returns trigger
+create or replace function web_contact_ins_trig_fun() returns trigger
as
$$
begin
new.modified := current_timestamp;
new.login_uc := UPPER(new.login);
- IF new.password <> old.password THEN
+
+ return new;
+end;
+$$
+language plpgsql;
+
+create or replace function web_contact_upd_trig_fun() returns trigger
+as
+$$
+begin
+ new.modified := current_timestamp;
+ new.login_uc := UPPER(new.login);
+ IF new.password IS DISTINCT FROM old.password THEN
new.old_password := old.password;
END IF;
@@ -32,9 +44,13 @@ language plpgsql;
create trigger
-web_contact_mod_trig
-before insert or update on web_contact
+web_contact_ins_trig
+before insert on web_contact
for each row
-execute procedure web_contact_mod_trig_fun();
-
+execute procedure web_contact_ins_trig_fun();
+create trigger
+web_contact_upd_trig
+before update on web_contact
+for each row
+execute procedure web_contact_upd_trig_fun();
commit 12595f9000c754d065b0609ca80340ce9624c857
Author: Tom Lane <tgl(a)redhat.com>
Date: Wed Apr 29 19:20:23 2009 -0400
Fix stub functions in .pks files that forgot to raise an exception.
Silently returning a bogus value is not a good idea.
diff --git a/schema/spacewalk/postgres/packages/rhn_bel.pks
b/schema/spacewalk/postgres/packages/rhn_bel.pks
index 3e5d852..e97dd34 100644
--- a/schema/spacewalk/postgres/packages/rhn_bel.pks
+++ b/schema/spacewalk/postgres/packages/rhn_bel.pks
@@ -24,8 +24,7 @@ create or replace function is_org_paid
)
returns numeric as $$
begin
- raise exception 'Stub called, must be replace by .pkb';
- return 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ language plpgsql;
@@ -35,8 +34,7 @@ create or replace function lookup_email_state
)
returns numeric as $$
begin
- raise exception 'Stub called, must be replace by .pkb';
- return 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/packages/rhn_config.pks
b/schema/spacewalk/postgres/packages/rhn_config.pks
index 7ea20a0..ff1986d 100644
--- a/schema/spacewalk/postgres/packages/rhn_config.pks
+++ b/schema/spacewalk/postgres/packages/rhn_config.pks
@@ -27,7 +27,7 @@ Create or replace function prune_org_configs
returns numeric
as $$
begin
- return 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ LANGUAGE 'plpgsql';
@@ -39,6 +39,7 @@ create or replace function delete_revision
returns void
as $$
begin
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ LANGUAGE 'plpgsql';
@@ -49,7 +50,7 @@ create or replace function get_latest_revision
returns numeric
as $$
begin
- return 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ LANGUAGE 'plpgsql';
@@ -61,7 +62,7 @@ create or replace function insert_file
returns numeric
as $$
begin
- return 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ LANGUAGE 'plpgsql';
@@ -69,6 +70,7 @@ create or replace function delete_file(config_file_id_in in numeric)
returns void
as $$
begin
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ LANGUAGE 'plpgsql';
@@ -83,7 +85,7 @@ create or replace function insert_channel
returns numeric
as $$
begin
- return 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ LANGUAGE 'plpgsql';
@@ -91,6 +93,7 @@ create or replace function delete_channel(config_channel_id_in in
numeric)
returns void
as $$
begin
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
end;
$$ LANGUAGE 'plpgsql';
diff --git a/schema/spacewalk/postgres/packages/rhn_date_manip.pks
b/schema/spacewalk/postgres/packages/rhn_date_manip.pks
index 36c4e0b..89f657d 100644
--- a/schema/spacewalk/postgres/packages/rhn_date_manip.pks
+++ b/schema/spacewalk/postgres/packages/rhn_date_manip.pks
@@ -7,8 +7,7 @@ create or replace function get_reporting_period_start()
returns timestamptz
as $$
begin
- raise exception 'Stub called, must be replace by .pkb';
- return current_timestamp;
+ raise exception 'Stub called, must be replaced by .pkb';
end;
$$ language 'plpgsql';
@@ -16,8 +15,7 @@ create or replace function get_reporting_period_end()
returns timestamptz as
$$
begin
- raise exception 'Stub called, must be replace by .pkb';
- return current_timestamp;
+ raise exception 'Stub called, must be replaced by .pkb';
end;
$$ language 'plpgsql';
diff --git a/schema/spacewalk/postgres/packages/rhn_exception.pks
b/schema/spacewalk/postgres/packages/rhn_exception.pks
index 94bf86c..ec991c2 100644
--- a/schema/spacewalk/postgres/packages/rhn_exception.pks
+++ b/schema/spacewalk/postgres/packages/rhn_exception.pks
@@ -7,7 +7,7 @@ CREATE OR REPLACE FUNCTION is_org_paid (org_id_in IN NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
- RETURN 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ LANGUAGE PLPGSQL;
@@ -15,6 +15,7 @@ CREATE OR REPLACE FUNCTION raise_exception(exception_label_in IN
VARCHAR)
RETURNS VOID
AS $$
BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ LANGUAGE PLPGSQL;
@@ -22,6 +23,7 @@ CREATE OR REPLACE FUNCTION raise_exception_val(exception_label_in IN
VARCHAR,val
RETURNS VOID
AS $$
BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ LANGUAGE PLPGSQL;
diff --git a/schema/spacewalk/postgres/packages/rpm.pks
b/schema/spacewalk/postgres/packages/rpm.pks
index fd39970..5d684eb 100644
--- a/schema/spacewalk/postgres/packages/rpm.pks
+++ b/schema/spacewalk/postgres/packages/rpm.pks
@@ -23,7 +23,7 @@ CREATE OR REPLACE FUNCTION isdigit(ch CHAR)
RETURNS BOOLEAN
AS $$
BEGIN
- RETURN FALSE;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END ;
$$ language 'plpgsql';
@@ -31,7 +31,7 @@ CREATE OR REPLACE FUNCTION isalpha(ch CHAR)
RETURNS BOOLEAN
AS $$
BEGIN
- RETURN FALSE;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language 'plpgsql';
@@ -39,7 +39,7 @@ CREATE OR REPLACE FUNCTION isalphanum(ch CHAR)
RETURNS BOOLEAN
AS $$
BEGIN
- RETURN FALSE;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language 'plpgsql';
@@ -47,7 +47,7 @@ CREATE OR REPLACE FUNCTION rpmstrcmp (string1 IN VARCHAR, string2 IN
VARCHAR)
RETURNS NUMERIC
AS $$
BEGIN
- RETURN 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END ;
$$ language 'plpgsql';
@@ -57,8 +57,7 @@ CREATE OR REPLACE FUNCTION vercmp(
RETURNS NUMERIC
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language 'plpgsql';
@@ -66,8 +65,7 @@ CREATE OR REPLACE FUNCTION vercmpCounter()
RETURNS NUMERIC
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language 'plpgsql';