Stored Procedure error in POSTGRESQL PGADMIN4

Question

I am getting the below error when I try to create either a stored procedure or a stored function in PGADMIN4. How can I fix it:

ERROR: syntax error at or near "procedure"

LINE 1: create or replace procedure transfer(

The code to create the tables:

drop table if exists accounts;
create table accounts (
    id int generated by default as identity,
    name varchar(100) not null,
    balance dec(15,2) not null,
    primary key(id)
);
insert into accounts(name,balance) values('Bob',10000);
insert into accounts(name,balance) values('Alice',10000);

Error is received after I add the below stored procedure:

create or replace procedure transfer(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$

Sorry, I couldn't add the code correctly above which was giving the problem: create or replace procedure transfer( sender int, receiver int, amount dec ) language plpgsql as $$ begin -- subtracting the amount from the sender's account update accounts set balance = balance - amount where id = sender; -- adding the amount to the receiver's account update accounts set balance = balance + amount where id = receiver; commit; end;$$ — beckythelearner
– beckythelearner, Commented Oct 17, 2020 at 21:36
Postgres 9.4 is no longer supported and it did not supported procedures. You should plan an upgrade as soon as possible. — user330315
– user330315, Commented Oct 17, 2020 at 21:58

GMB · Accepted Answer · 2020-10-17 21:54:43Z

3

Procedures are available in Postgres starting version 11 only, while you tagged your question with version 9.4.

In earlier versions, you can use a void function instead. Basically, replace:

create or replace procedure transfer(sender int, receiver int, amount dec)
language plpgsql    
as $$
begin
    ...
end; $$

With:

create or replace function transfer(sender int, receiver int, amount dec)
returns void
language plpgsql    
as $$
begin
    ...
end; $$

answered Oct 17, 2020 at 21:54

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Stored Procedure error in POSTGRESQL PGADMIN4

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related