Syntax error at or near "IF" in PostgreSQL

Question

I am trying to write an sql function on PostgreSQL, but I have an error with the 'IF':

ERROR: Syntax error at or near "IF"
LINE 11: IF Type = 's' THEN

I can't understand the syntax error.
(The Function without the IF work correctly)

My SQL Code:

CREATE OR REPLACE FUNCTION public.add_activity(IDactivity smallint,Date_Start date, Data_End date, 
Type character varying,Name character varying DEFAULT NULL::character varying,
Typology character varying DEFAULT NULL::character varying, Client smallint DEFAULT NULL::smallint)
RETURNS void
LANGUAGE 'sql'
AS $BODY$

INSERT INTO public."Activity" 
VALUES(IDactivity, Date_Start, Data_End, Type, Name);

IF Type = 's' THEN
INSERT INTO public."Service" 
VALUES(IDactivity, Typology, Client); 

END IF;
$BODY$;

Thank you!

You cannot use if when you set the function language to SQL. You enable it using LANGUAGE plpgsql — coladict
– coladict, Commented Jan 7, 2022 at 16:29

Edouard · Accepted Answer · 2022-01-07 16:30:21Z

2

IF is not implemented in the sql language but it is in the plpgsql language, see the manual.

You can either replace LANGUAGE sql by LANGUAGE plpgsql and then add BEGIN and END in the function body :

CREATE OR REPLACE FUNCTION public.add_activity(IDactivity smallint,Date_Start date, Data_End date, 
Type character varying,Name character varying DEFAULT NULL::character varying,
Typology character varying DEFAULT NULL::character varying, Client smallint DEFAULT NULL::smallint)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
BEGIN
INSERT INTO public."Activity" 
VALUES(IDactivity, Date_Start, Data_End, Type, Name);

IF Type = 's' THEN
INSERT INTO public."Service" 
VALUES(IDactivity, Typology, Client); 
END IF;

END ;
$BODY$

Or you can change your code to stay in the sql language :

CREATE OR REPLACE FUNCTION public.add_activity(IDactivity smallint,Date_Start date, Data_End date, 
Type character varying,Name character varying DEFAULT NULL::character varying,
Typology character varying DEFAULT NULL::character varying, Client smallint DEFAULT NULL::smallint)
RETURNS void
LANGUAGE sql
AS $BODY$

INSERT INTO public."Activity" 
VALUES(IDactivity, Date_Start, Data_End, Type, Name);

INSERT INTO public."Service"
SELECT IDactivity, Typology, Client
WHERE Type = 's' ;

$BODY$

edited Jan 7, 2022 at 16:30

answered Jan 7, 2022 at 16:23

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

1 Comment

Marco Bagiacchi Over a year ago

I used the second version, it was much simpler than I thought. I feel stupid, but after 10 hours on the screen my brain disconnected :D

Collectives™ on Stack Overflow

Syntax error at or near "IF" in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related