Postgresql update query syntax error in plpgsql function

Question

I have to create a update procedure, so i created a function and given only below query

 CREATE FUNCTION public.testf()
    RETURNS boolean
    LANGUAGE 'plpgsql'

AS $BODY$begin
update tt pp
set status  = 'Ok'
return true;
end;$BODY$;

ALTER FUNCTION public.testf()
    OWNER TO postgres;

returns error

ERROR: syntax error at or near "UPDATE" LINE 5: AS $BODY$UPDATE

return type I have given is int4range

What I am doing wrong

Please help THanks

That's not a procedure/function, that's a single SQL query. Post the complete code. — Bergi
– Bergi, Commented May 11, 2020 at 12:42
Also, is your table really named table? That might cause issues, as it is a keyword. Use UPDATE "table" … — Bergi
– Bergi, Commented May 11, 2020 at 12:42

Laurenz Albe · Accepted Answer · 2020-05-11 12:49:31Z

2

Pulling out my crystal ball, I see:

You created the function with LANGUAGE plpgsql.
You didn't surround the function body with BEGIN ... END;.

If your function is just a single SQL statement, use LANGUAGE sql.

Else, use the block-centered PL/pgSQL syntax properly.

answered May 11, 2020 at 12:49

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

Laurenz Albe Over a year ago

Ok, my crystal ball cannot penetrate that deep. You'll have to edit the question and show the complete function and the error message.

Bergi Over a year ago

I think you hit the nail on the head, and the next error message will be that neither implementation will return a int4range.

Laurenz Albe Over a year ago

Yes. Where should that int4rangecome from?

Md. Parvez Alam Over a year ago

I have updated post whatever its showing in sql tab

user330315 · Accepted Answer · 2020-05-12 05:59:36Z

1

You are missing an ; to end the UPDATE statement.

CREATE FUNCTION public.testf()
    RETURNS boolean
    LANGUAGE plpgsql
AS $BODY$
begin
  update tt pp
    set status  = 'Ok';  --<< here

  return true;
end;
$BODY$;

answered May 12, 2020 at 5:59

user330315

Collectives™ on Stack Overflow

Postgresql update query syntax error in plpgsql function

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related