From: | "Thalis A(dot) Kalfigopoulos" <thalis(at)cs(dot)pitt(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Pl/pgsql function fails when false |
Date: | 2001-06-21 21:24:06 |
Message-ID: | Pine.LNX.4.21.0106211708420.24987-100000@aluminum.cs.pitt.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
The following pl/pgsql function looks in a particular table (arg1) under a particular attribute (arg2) if a particular value exists (arg3) and returns true/false accordingly. The following implementation returns true if element is found, but fails with "ERROR: control reaches end of function without RETURN" if it isn't found instead of returning false.
CREATE FUNCTION myexists(varchar(20),varchar(20),int4) RETURNS bool AS
'DECLARE
query text;
tmp RECORD;
tabname ALIAS FOR $1;
colname ALIAS FOR $2;
value ALIAS FOR $3;
BEGIN
query := ''SELECT * FROM ''||quote_ident(tabname)||'' WHERE ''||quote_ident(colname)||''=''||quote_literal(value);
FOR tmp IN EXECUTE query LOOP
IF NOT FOUND THEN
RETURN ''false''::bool;
ELSE
RETURN ''true''::bool;
END IF;
END LOOP;
END;' LANGUAGE 'plpgsql';
So it seems it's neglecting the "RETURN ''false''::bool" statement
I made it work in the end as:
CREATE FUNCTION myexists(varchar(20),varchar(20),int4) RETURNS bool AS
'DECLARE
query text;
tmp RECORD;
tabname ALIAS FOR $1;
colname ALIAS FOR $2;
value ALIAS FOR $3;
BEGIN
query := ''SELECT * FROM ''||quote_ident(tabname)||'' WHERE ''||quote_ident(colname)||''=''||quote_literal(value);
FOR tmp IN EXECUTE query LOOP
IF NOT FOUND THEN
EXIT;
ELSE
RETURN ''true''::bool;
END IF;
END LOOP;
RETURN ''false''::bool;
END;' LANGUAGE 'plpgsql';
Any ideas why the one would fail while the other would work?
cheers,
thalis
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-06-21 21:56:36 | Re: Pl/pgsql function fails when false |
Previous Message | Tom Lane | 2001-06-21 21:21:43 | Re: index on a box |