Pl/pgsql function fails when false

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

Responses

Browse pgsql-general by date

  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