PostgreSQL Function Syntax Error in DECLARE clause

Question

I am unable to create this function as I get syntax error near INTEGER in line 6. Am I missing something here?

CREATE OR REPLACE FUNCTION public.update_application_status_by_token(applicationToken integer, userId integer, newStatus data.applicationstatus)
  RETURNS BOOLEAN
  LANGUAGE sql
AS $function$
    DECLARE
      applicationId INTEGER;
    BEGIN
      SELECT id INTO applicationId FROM data.listings_applications WHERE token = applicationToken;
      INSERT INTO public.listings_applications_status(application_id, user_id, status)  VALUES (applicationId, userId, newStatus);
      UPDATE public.listings_applications SET status = newStatus WHERE token=applicationToken;
      RETURN TRUE;
      EXCEPTION WHEN OTHERS THEN
        RETURN FALSE;
    END;
$function$

worked! Thanks, add your ans below so I can mark it as correct. — Ahmed H. Saab
– Ahmed H. Saab, Commented Dec 1, 2016 at 9:48

Laurenz Albe · Accepted Answer · 2016-12-01 09:48:15Z

4

This is a PL/pgSQL function, so you must declare it as such.

Use

LANGUAGE plpgsql

rather than

LANGUAGE sql

answered Dec 1, 2016 at 9:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

Vao Tsun · Accepted Answer · 2016-12-01 10:19:20Z

1

change

LANGUAGE sql

to

LANGUAGE plpgsql

to make it work. SQL DECLARE is for cursors. In your function it is plpgslq command

answered Dec 1, 2016 at 10:19

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Function Syntax Error in DECLARE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related