| 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: | Whole Thread | Raw Message | 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 |