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

Re: "I want to test it first, BEFORE I put it in a stored procedure": Why? When you're testing C# code, do you refuse to create a Main method? — ruakh
– ruakh, Commented Feb 5, 2012 at 18:39
@ruakh: No, but I refuse to make a function for every bit of code (with all parameters), and then have to call this function from the main method every time i need to test some code. (and afterwards having to remove those functions as well). Basically, all I want to do is run CREATE LANGUAGE plpgsql; if it is not a registered language at the start of a script. But this seems hardly possible, if there is no if statement... — Stefan Steiger
– Stefan Steiger, Commented Feb 5, 2012 at 18:44
There is no PRINT statement in Postgres. And PL/pgSQL is always created since 9.0. So you only need to test for it, if you target previous versions — user330315
– user330315, Commented Feb 5, 2012 at 19:07
@a_horse_with_no_name: True as I saw, it's just called RAISE NOTICE however. One question that remains then is why is print highlighted, then. Ironically it ran on the production server, but failed on the development server, because the production server is brand new, and development is old. In general, you never know what version a potential customer will have/accept. — Stefan Steiger
– Stefan Steiger, Commented Feb 5, 2012 at 19:59

mu is too short · Accepted Answer · 2012-02-05 19:15:11Z

11

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.

answered Feb 5, 2012 at 19:15

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

4 Comments

Stefan Steiger Over a year ago

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

user330315 Over a year ago

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 Over a year ago

@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 Over a year ago

@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.

Collectives™ on Stack Overflow

How to test an if statement in PostgreSQL?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related