Обсуждение: exception problem

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.

java4dev <java4dev@gmail.com> 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

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.

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.