Cannot execute a postgres function - syntax error at IF

Question

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;

Just leave it out? (the IF and the ELSE block contain the same query) — wildplasser
– wildplasser, Commented Mar 23, 2014 at 15:47
Your if doesn't make sense. It has a group by, suggesting that it will return multiple rows, but it is in a context where only a scalar subquery can be used. — Gordon Linoff
– Gordon Linoff, Commented Mar 23, 2014 at 16:16
IF ... THEN ... ELSE ... END IF; <- you are missing THEN — Gábor Buella
– Gábor Buella, Commented Mar 23, 2014 at 18:06

Craig Ringer · Accepted Answer · 2014-03-24 01:34:26Z

1

You can't use IF at the top level in standard SQL, or in PostgreSQL's SQL dialect.

Use CASE .. WHEN ... THEN ... END instead, or use the PL/PgSQL language. PL/PgSQL allows you to use IF statements.

Separately, this:

 COUNT (gift_lists.gift_lists_id) as number_giftlists < 5

makes no sense. I think you mean:

SELECT
  gift_lists.users_id,
  COUNT (gift_lists.gift_lists_id) as number_giftlists
FROM gift_lists 
  LEFT  JOIN users ON gift_lists.users_id = users.users_id 
GROUP BY gift_lists.users_id
HAVING COUNT (gift_lists.gift_lists_id) < 5

answered Mar 24, 2014 at 1:34

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot execute a postgres function - syntax error at IF

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related