The following pl/pgsql function looks in a particular table (arg1) under a particular attribute (arg2) if a particular
valueexists (arg3) and returns true/false accordingly. The following implementation returns true if element is found,
butfails 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
