I'm trying to run a store procedure/function on my database that shows all the users with giftlists < 5. I get an error "syntax error at or near IF" I've run the SQL statements separately and it works fine.
--function counts the number of giftlists per user
--and put their names into a new table
CREATE OR REPLACE FUNCTION foo()
RETURNS TABLE(users_id users.users_id%TYPE, first_name users.first_name%TYPE, last_name users.last_name%TYPE) AS
$func$
BEGIN
-- if number of giftlists < 5
IF(SELECT gift_lists.users_id, COUNT (gift_lists.gift_lists_id) as number_giftlists < 5 FROM gift_lists LEFT JOIN users ON gift_lists.users_id =
users.users_id GROUP BY gift_lists.users_id)
RETURN QUERY
SELECT v.users_id, v.first_name, v.last_name
FROM my_view v;
ELSE
RETURN QUERY
SELECT v.users_id, v.first_name, v.last_name
FROM my_view v;
END IF;
END
$func$ LANGUAGE sql STABLE;
ifdoesn't make sense. It has agroup by, suggesting that it will return multiple rows, but it is in a context where only a scalar subquery can be used. – Gordon Linoff Mar 23 '14 at 16:16IF ... THEN ... ELSE ... END IF;<- you are missingTHEN– Buella Gábor Mar 23 '14 at 18:06