CODEWITHSUNDEEP
postgresqlplpgsql
Marco Bagiacchi
Marco Bagiacchi

Reputation: 334

Syntax error at or near "IF" in PostgreSQL

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!

Upvotes: 0

Views: 1002

Answers (1)

Edouard
Edouard

Reputation: 7065

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$

Upvotes: 2

Related Questions