current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 10 down vote favorite
2

I'm new with PostgreSQL, and I already have my first problem..

I wrote some code to understand how transactions work, following the manual step by step.

To make it short, I've created 2 tables, user and movements: in the first one there are the name, email and credit columns, in the second the columns from, to, import.

So, I was trying this way:

BEGIN;
INSERT INTO movements (from, to, import) VALUES ('mary', 'steve', 600);
UPDATE users SET credit = credit - 600 WHERE name = 'mary';
UPDATE users SET credit = credit + 600 WHERE name = 'steve';
--here comes the problem!
IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
 ROLLBACK;
END IF
COMMIT;

I always get the error:

ERROR: syntax error at or near "IF"

Where am I mistaken?

P.S.: Don't focus on the example functionality, it's just a trial for me to understand the transactions.. and now, the IF clause...

share|improve this question
    
I have added sample code, by your request. This ought to help you out. :-) – pyrocumulus Apr 29 '09 at 21:58
    
I think others have answered what the problem with the syntax is, but I have a comment. It looks like the invariant that your code is trying to maintain could be maintained with a check constraint on the credit column. – Warren Apr 30 '09 at 4:41
    
Use a check constraint, then you don't need this construction. – Frank Heikens Apr 29 '10 at 8:56
    
@frank: i was trying to learn hot transactions works, that's just an example ;) – Strae Apr 29 '10 at 9:23

5 Answers 5

up vote 6 down vote accepted

As Johannes already says: you are mixing regular SQL with PL/pgSQL, the stored procedure language. The link that Johannes provides should explain the concept of stored procedures to you.

I take it you're doing this as a script? Executing one statement after another? I'm afraid you can only do what you want to do inside a Stored Procedure, or Function, as you might call it. This is because when you are executing statements in this way, every statement stands on its own with no relation or information regarding the other statements.

Furthermore you can look at the following link for more information on how to use IF ... THEN ... ELSE ... END IF; conditionals inside plpgsql: link.

EDIT:

I don't know if ROLLBACK is allowed at that point (because each stored procedure is already in its own transaction), but you must be able to figure that out for yourself using the extensive documentation @ http://www.postgresql.org. Here's a sample function with your code in it, also demonstrating some other syntax:

CREATE OR REPLACE FUNCTION public.test()
RETURNS integer AS
$$
DECLARE
tempvar integer;

BEGIN    
     tempvar := 1;

     INSERT INTO movements (from, to, import) VALUES ('mary', 'steve', 600);
     UPDATE users SET credit = credit - 600 WHERE name = 'mary';
     UPDATE users SET credit = credit + 600 WHERE name = 'steve';

     --here comes the problem!
     IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
        ROLLBACK;
     END IF;

     RETURN tempvar;
END
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

However, if you are really going this road, I recommend using a GUI DB manager. It's easier for learning all this.

share|improve this answer
    
Ok, i have understood and im reading the johannes link (that i didn't see first time i read the answer). Can you post an example of how may look the sql i wrote in PL/pgSQL? Thanks.. – Strae Apr 29 '09 at 15:21
    
Thanks mate, i've found the postgres documentation very good and well-written.. just little 'big', but that's ok, i've purchased the 3 volume paper version and i promise to read them all. I just cant understand why the if statement cant be used in plain sql when, in mysql, it can ben used as well.. – Strae Apr 30 '09 at 6:19
    
Ho, last thing: i've googled around and even in the operator section of the manual, but i cant find the meaning of ':='.. what's that used for? – Strae Apr 30 '09 at 6:25
    
The operator ':=' is used for assigning variables their values without a corresponding query. – pyrocumulus Apr 30 '09 at 18:30
up vote 3 down vote

You seem to use plain SQL but the IF statement is part of the PL/pgSQL procedural language which is part of PostgreSQL.

share|improve this answer
1  
can explain better that? – Strae Apr 29 '09 at 15:05
up vote 1 down vote

If you want to avoid the if you could rewrite your code as:

BEGIN;

    INSERT INTO movements (from, to, import)    
    SELECT 'mary', 'steve', CASE credit < 600 WHEN TRUE THEN 0 ELSE 600 END;

    UPDATE users SET credit = credit - CASE credit < 600 WHEN TRUE THEN 0 ELSE 600 END    
    WHERE name = 'mary';

    UPDATE users u SET u.credit = u.credit + CASE v.credit < 600 WHEN TRUE THEN 0 ELSE 600 END    
    FROM users v    
    WHERE u.name = 'steve' and v.name = 'mary'

COMMIT;

Yes, this is stupid :) .

share|improve this answer
    
lol im new of postgresql.. i think to have understood your example ;) – Strae Apr 29 '09 at 15:26
    
It's a terrible example, really, it was just a thought experiment :) . – Adam Luter Apr 29 '09 at 16:10
up vote 1 down vote

You could try to modify the IF part, from:

IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
 ROLLBACK;
END IF

to

SELECT SUM(credit) INTO v_credit FROM users WHERE name = 'mary';
IF (v_credit) < 0 THEN
 ROLLBACK;
END IF

Assuming v_credit is a variable you defined previously. IMHO, Postgre assumes SELECT query returns more than one result, even though you're very certain that it's unique. So I think you could try to assign the value to a variable first beforehand.

share|improve this answer
    
for testing it, i've even tryed the IF 2 = 2 THEN [...] but still throw the error.. – Strae Apr 30 '09 at 6:17
up vote 0 down vote

Similar to Microsoft's SQL and T/SQL, you should be able to mix regular SQL with PL/pgSQL if they are in the correct sequence. Here's an example where the sequence matters in a mixed SQL/PL stored proc:

You cannot wrap conditional statements inside the cursor--you must put the cursor inside the conditional statement. If you do the sequence the other way around, you will get the same error as you'd seen, 'ERROR: syntax error at or near "IF"':

CREATE OR REPLACE FUNCTION getSubsystemFaultListCount(_bunoid integer, _subsystem text, _starttime timestamp without time zone, _stoptime timestamp without time zone)
      RETURNS refcursor AS
    $BODY$
    DECLARE mycurs refcursor;
    BEGIN 
        IF _subsystem = 'ALL' THEN
            OPEN mycurs FOR
            SELECT  count(*), fs_fault.faultcode, fs_fault.downloadtime
            FROM    fs_fault
            WHERE   fs_fault.bunoid = _bunoid
                AND fs_fault.statusid IN(2, 4)
                AND fs_fault.downloadtime BETWEEN _starttime AND _stoptime
            GROUP BY fs_fault.faultcode, fs_fault.downloadtime;
            RETURN mycurs;
        ELSE
            OPEN mycurs FOR
            SELECT  count(*), fs_fault.faultcode, fs_fault.downloadtime
            FROM    fs_fault
            WHERE   fs_fault.bunoid = _bunoid
                AND fs_fault.subsystemid 
                    IN(SELECT id FROM fs_subsystem WHERE type = _subsystem)
                AND fs_fault.statusid IN(2, 4)
                AND fs_fault.downloadtime BETWEEN _starttime AND _stoptime
            GROUP BY fs_fault.faultcode, fs_fault.downloadtime;
            RETURN mycurs;
        END IF;

    END;
    $BODY$

I am a beginner in PostGresSQL; this function is just an example.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.