CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.4sql-function
Ahmed H. Saab
Ahmed H. Saab

Reputation: 413

PostgreSQL Function Syntax Error in DECLARE clause

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$

Upvotes: 1

Views: 2318

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51446

change

LANGUAGE sql

to

LANGUAGE plpgsql

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

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246238

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

Use

LANGUAGE plpgsql

rather than

LANGUAGE sql

Upvotes: 4

Related Questions