Postgres - ERROR: syntax error at or near "IF"

Question

I have a function and I need to do different select based on inputid. so I have added the condition as below.

....
begin
IF(iv_binrocess = 1 AND IsNull(iv_binno,'') != '') THEN
...//some process here 
END If;
...
End

When I run the function, I got the error "

ERROR: syntax error at or near "IF" I referred many sites and tutorials, nothing helped me.

Can anyone help me to fix this issue?

Thanks

I suspect that you have stripped enough code from the sample you posted to strip the problem with it. Please post the complete code surrounding the error. Are you sure that you are looking at the correct IF in the function body? — Laurenz Albe
– Laurenz Albe, Commented Jul 26, 2017 at 11:14
Show us your complete code. Edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Jul 26, 2017 at 11:36
Also, you added a tag of Postgres, but Postgres does not have IsNull function, its equivalent in Postgres is coalesce – see documentation. — Kristo Mägi
– Kristo Mägi, Commented Jul 26, 2017 at 12:32

Craig Ringer · Accepted Answer · 2017-07-26 14:54:18Z

1

WAG, since you deleted most of the important information: You're trying to create a function with a PL/pgsql body, but declaring it SQL.

craig=> CREATE FUNCTION test() RETURNS void LANGUAGE sql AS 
$$ BEGIN IF TRUE THEN PERFORM 1; END IF; END; $$ ;
ERROR:  syntax error at or near "IF"
LINE 1: ...TION test() RETURNS void LANGUAGE sql AS $$ BEGIN IF TRUE TH...

Declare PL/PgSQL functions as LANGUAGE plpgsql.

If you instead want to use an SQL function, use a CASE expression instead of IF.

CREATE FUNCTION test2() RETURNS integer LANGUAGE sql 
AS $$ SELECT CASE WHEN TRUE THEN 1 ELSE 0 END; $$;

(Note, I haven't bothered to format these readably. Don't write functions all in one line like this in real code.)

answered Jul 26, 2017 at 14:54

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Postgres - ERROR: syntax error at or near "IF"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related