ERROR: syntax error at or near "IF"... why?

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.

On 30 Apr, 07:30, Johan Nel <johan555.nel...@xsinet555.co.za> wrote:

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.

I tryed the declare, before and after the BEGIN;, but allways returns
me the error:
-----------------------------------------------------------
ERROR: syntax error at or near "int";
LINE 1: DECLARE _mycredit int;
^
-----------------------------------------------------------

For the if statement, i've tryed that
IF 2 = 2 THEN
ROLLBACK;
END IF

but still the error near the "IF"..
dunno.. in mysql the if can be used in that way.. in postgres no?

DaNieL..! wrote:

I tryed the declare, before and after the BEGIN;, but allways returns
me the error:
-----------------------------------------------------------
ERROR: syntax error at or near "int";
LINE 1: DECLARE _mycredit int;
^
-----------------------------------------------------------

For the if statement, i've tryed that
IF 2 = 2 THEN
ROLLBACK;
END IF

but still the error near the "IF"..
dunno.. in mysql the if can be used in that way.. in postgres no?

Sorry was thinking FUNCTION way of doing it. DECLARE can only be used
in a stored procedure/function.

Johan

On 30/04/2009 07:45, DaNieL..! wrote:

I tryed the declare, before and after the BEGIN;, but allways returns
me the error:
-----------------------------------------------------------
ERROR: syntax error at or near "int";
LINE 1: DECLARE _mycredit int;
^
-----------------------------------------------------------

I missed what came before in this thread, but in plpgsql functions the
DECLARE comes before BEGIN:

create or replace function my_function() returns....
as
$$
declare
....
begin
....
return....
end;
$$
language plpgsql;

Can you show us the full function code again please?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Thu, Apr 30, 2009 at 1:45 AM, DaNieL..! <daniele.pignedoli@gmail.com> wrote:

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"

if you're inside a server-side function then you cannot use COMMIT nor
ROLLBACK; if you aren't inside a server-side function then you cannot
use IF

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

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/