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

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/