How to test an if statement in PostgreSQL?

Question

Question: I want to test an if statement in PostgreSQL:

IF (SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql') > 0 THEN
    PRINT 'Good'
ELSE
    PRINT 'Bad'
END IF;

Now this throws an error at IF.

As far as I have read, this is because I need to use plpgsql to be able to use if, print, and variables.

So far, I probably also have to use SELECT instead of print as well.

How can I switch the language before executing this statement to plpgsql ?

I want to test it first, BEFORE I put it in a stored procedure. To test code with variables etc.

Edit:

Solved by:

DO LANGUAGE plpgsql $$
    BEGIN
        IF (SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql') > 0 THEN 
            RAISE NOTICE 'GOOD';
        ELSE
            RAISE NOTICE 'BAD';
        END IF;
    END;
$$;

Solution

If you just want to test code snippets without going through all the hassle of building and dropping a function, then you can use DO:

=> do language plpgsql $$
    begin
        -- Yes, I have a table called pancakes in my playpen database.
        if (select count(*) from pancakes) > 0 then
            raise notice 'Got some';
        else
            raise notice 'Got none';
        end if;
    end;
$$;

You'll need 9.0+ to use DO.

Comments

[Stefan Steiger]

It's only for development, so 9.0+ is OK.

[a_horse_with_no_name]

But if you have 9.0, there is no need to check for plpgsql (because it's installed by default). And for versions prior to 9.0 you do not have the DO statemen.t

[mu is too short]

@a_horse_with_no_name: I think you're focusing on the wrong thing, the query itself is a distraction, the real problem is hidden at the end: "To test code with variables etc.". OP seems to be more interested in a REPL for psql.

[Stefan Steiger]

@a_horse_with_no_name: Wrong, you only do NOT need to check if deployment is going to be on your machine (if you know that it's 9.0+), but if you're also deploying on other machines, where version might be < 9.0... And the DO statement is really only for development (which is on 9.0), so it doesn't matter for deployment on < 9.0.