Pl/pgsql function fails when false

Started by Thalis A. Kalfigopoulosalmost 25 years ago2 messagesgeneral
Jump to latest
#1Thalis A. Kalfigopoulos
thalis@cs.pitt.edu

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Thalis A. Kalfigopoulos (#1)
Re: Pl/pgsql function fails when false

"Thalis A. Kalfigopoulos" <thalis@cs.pitt.edu> writes:

Any ideas why the one would fail while the other would work?

The first one is wrong.

Hint: the loop executes zero times if the SELECT doesn't find anything.

regards, tom lane