CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Stefan Steiger
Stefan Steiger

Reputation: 82186

How to test an if statement in PostgreSQL?

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;
$$;

Upvotes: 7

Views: 19592

Answers (1)

mu is too short
mu is too short

Reputation: 434665

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.

Upvotes: 11

Related Questions