Обсуждение: ERROR: syntax error at or near "IF"... why?

Hi guys, im new with postgresql, and already got my first problem..

Well, I wroted some code for understend how the transaction works,
following step by step the manual.

TO make it short, i've created 2 tables, user and movements: in the
firs one there are the name, email and credit colons, in the second
the colons from, to, import.

So, i was triyng that 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: dont focus on the example functionality, its just a trial for me
to understand the transactions.. and now, the IF clause...

Daniel,

> 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?

SELECT returns in essence a record or setof records.

DECLARE _credit int;
...
SELECT credit FROM users WHERE name = 'mary' INTO _credit;
IF _credit < 0 THEN
   ROLLBACK;
END IF;

If there is a chance that the select returns more than one record you
can do something similar to:
DECLARE rec record;
...
FOR rec IN (SELECT credit FROM users WHERE name = 'mary'
LOOP
   IF rec.credit < 0 THEN
     ...
   ELSE
     ...
   END IF;
END LOOP;

HTH,

Johan Nel
Pretoria, South Africa.

Could if be referencing the second IF..the one in your "END IF" that doesn't
have a semicolon after it...?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of DaNieL
Sent: Wednesday, April 29, 2009 9:54 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] ERROR: syntax error at or near "IF"... why?

Hi guys, im new with postgresql, and already got my first problem..

Well, I wroted some code for understend how the transaction works,
following step by step the manual.

TO make it short, i've created 2 tables, user and movements: in the
firs one there are the name, email and credit colons, in the second
the colons from, to, import.

So, i was triyng that 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: dont focus on the example functionality, its just a trial for me
to understand the transactions.. and now, the IF clause...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Apr 29, 2009 at 07:54:20AM -0700, DaNieL wrote:
> ERROR:  syntax error at or near "IF"
>
> Where am i mistaken?
>
> p.s: dont focus on the example functionality, its just a trial for me
> to understand the transactions.. and now, the IF clause...

As others have said, IF statements are only valid in plpgsql functions,
not in plain SQL.  Probably not relevant but in this example can work
around it by doing something like:

  CREATE FUNCTION failif(BOOL,TEXT) RETURNS VOID AS $$
    BEGIN IF $1 THEN RAISE EXCEPTION '%', $2; END IF; END $$ LANGUAGE plpgsql;

  BEGIN;
  INSERT INTO ...
  UPDATE ...
  UPDATE ...
  SELECT failif((SELECT credit FROM users WHERE name = 'mary') < 0,
                'error, credit can't be less than zero');
  COMMIT;

In general, you're probably better off writing the whole thing in
plpgsql.

Hope that helps.

--
  Sam  http://samason.me.uk/