1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Rep Power

    Sum IF Support by postgres

    I want to do similar query like the one mention in devshed article by using postgres instead of mysql.

    This is the article location.

    But I keep getting this error message.
    Error: ERROR: Function if(boolean, integer, integer) does not exist
    Unable to identify a function that satisfies the given argument types
    You may need to add explicit typecasts

    I have no idea what this error mean.
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Los Angeles
    Rep Power
    Ok, I guess there's a few things to address:

    1)The error message. The error message is saying that Postgres does not recognize the IF function you are trying to use, meaning that there is no IF function, or the paramaters that you are passing to the IF function are of the wrong type and may need casting to the correct types. Off the top of my head I don't know if Postgres has an exact corollary to that MySQL IF function, perhaps one of the mods or more experienced PG developers can answer that.

    2)How can you get the same type of resulting output from Postgres ?
    You can probably do (basing my db structure off of the link you gave in here
    Try something like this (note untested query as I don't have a DB like this lying around):

    SELECT location, SUM(CASE gender='M' THEN 1 ELSE 0 END) AS M, SUM(CASE gender='F' THEN 1 ELSE 0 END) AS F FROM locations, employees WHERE locations.loc_code = employees.loc_code GROUP BY location;

    HTH helps,
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Central Florida, USA
    Rep Power
    Yes, there is no IF function in PostgreSQL that works the way MySQL's IF function works. But, in PostgreSQL, you can do all that and more with the CASE and COALESCE expressions. In fact, COALESCE works very similarly to the MySQL IF function (although more logically, IMHO), while CASE allows for true multi-line control structures, similar to if-else constructs.

    If you want more control structure functionality, you can always write a stored procedure in PL/PgSQL.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo