From: | java4dev <java4dev(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | exception problem |
Date: | 2011-02-22 17:34:41 |
Message-ID: | 1298396081219-3395874.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello I have the following table
CREATE TABLE aaa.domains (
pk_domain_id INTEGER NOT NULL,
domain_name CHARACTER VARYING(254) NOT NULL,
is_unlocked BOOLEAN DEFAULT TRUE NOT NULL, -- LOCKED = 0, UNLOCKED = 1
fk_owner_user_id INTEGER DEFAULT NULL, -- owner
CONSTRAINT IXP_PK_DOMAIN_ID PRIMARY KEY (pk_domain_id)
);
-- ALTER TABLE aaa.domains ADD CONSTRAINT IXP_PK_DOMAIN_ID PRIMARY KEY
(pk_domain_id);
CREATE UNIQUE INDEX IXU_DOMAINS_DOMAIN_NAME ON aaa.domains (domain_name);
CREATE INDEX IX_DOMAINS_OWNER_USER_ID ON aaa.domains (fk_owner_user_id);
CREATE SEQUENCE aaa.seq_domains
START WITH 1000
INCREMENT BY 1
NO CYCLE
OWNED BY aaa.domains.pk_domain_id;
ALTER TABLE aaa.seq_domains OWNER TO someuser;
and wrote the following stored procedure
CREATE OR REPLACE FUNCTION aaa.is_domain_unlocked(p_domain_id
aaa.domains.pk_domain_id%TYPE)
RETURNS BOOLEAN
AS $$
DECLARE
v_is_unlocked aaa.domains.is_unlocked%TYPE;
BEGIN
SELECT is_unlocked INTO v_is_unlocked
FROM aaa.domains
WHERE pk_domain_id = p_domain_id;
RETURN v_is_unlocked;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
RETURN FALSE;
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
$$ LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION aaa.is_domain_unlocked(integer) OWNER TO someuser;
now without any records by calling
SELECT aaa.is_domain_unlocked(1);
I expect to get back a FALSE but instead I get a row with nothing which I
assume is NULL value;
is_domain_locked
------------------
(1 row)
The catch exception doesn't seem to work and I even tried to RAISE EXCEPTION
but I couldn't find any information on how to rethrow an exception.
Throwning any other exception doesn't seem to have any effect.
thank you
Nikolas
--
View this message in context: http://postgresql.1045698.n5.nabble.com/exception-problem-tp3395874p3395874.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-02-22 17:42:37 | Re: simple test code |
Previous Message | java4dev | 2011-02-22 17:24:09 | simple test code |