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.

Search Discussions

  • Tom Lane at Feb 22, 2011 at 5:48 pm

    java4dev writes:
    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;
    Perhaps you want "if found then return v_is_unlocked; else return false;
    end if;" in there. Or if you really want to do it as above, you need to
    say SELECT INTO STRICT to have those exceptions be thrown. See the
    manual.

    regards, tom lane
  • Java4dev at Feb 22, 2011 at 6:04 pm
    Thank you Tom,

    adding STRICT to INTO gave the correct result but why didn't the
    EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    RETURN FALSE;

    worked?
    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/exception-problem-tp3395874p3395914.html
    Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
  • Java4dev at Feb 22, 2011 at 6:11 pm
    OBS sorry I meant
    WHEN NO_DATA_FOUND THEN
    RETURN FALSE;

    but I read in the manual that in order to get the exceptions you must set
    the STRICT option.
    I misunderstood it the first time.
    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/exception-problem-tp3395874p3395932.html
    Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 22, '11 at 5:34p
activeFeb 22, '11 at 6:11p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Java4dev: 3 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase