Skip site navigation (1) Skip section navigation (2)

exception problem

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 (view raw or flat)
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.

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2011-02-22 17:42:37
Subject: Re: simple test code
Previous:From: java4devDate: 2011-02-22 17:24:09
Subject: simple test code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group