I'm creating a stored procedure in PostgreSQL, but I get an error 'syntax error at or near "UPDATE"'

Question

I'm creating a stored procedure that changes email address, but I keep getting an error.

ERROR: syntax error at or near "UPDATE"

CREATE FUNCTION change(
IN oldAddr VARCHAR(50),
IN newAddr VARCHAR(50)
) AS
UPDATE accounts
SET a_email = newAddr
WHERE a_email = oldAddr;

I copied this from the textbook, but I don't think it works on PostgreSQL.
Please teach me how to correct it! Thank you!

CREATE TABLE accounts (
a_id int NOT NULL PRIMARY KEY,
a_first_name varchar(25) NOT NULL,
a_last_name varchar(25) NOT NULL,
a_email varchar(50) NOT NULL,
a_password varchar(16) NOT NULL
);

peterm · Accepted Answer · 2013-12-11 04:43:57Z

7

Try it this way

CREATE OR REPLACE FUNCTION change_account_email(
  IN oldAddr VARCHAR(50),
  IN newAddr VARCHAR(50))
RETURNS INTEGER AS 
$$
DECLARE
  rcount INTEGER DEFAULT 0;
BEGIN
  UPDATE accounts
     SET a_email = newAddr
   WHERE a_email = oldAddr;
   GET DIAGNOSTICS rcount = ROW_COUNT;
   RETURN rcount;
END;
$$
LANGUAGE plpgsql;

Sample usage:

SELECT change_account_email('[email protected]', '[email protected]');

Let's try it:

# INSERT INTO accounts VALUES(1, 'John', 'Doe', '[email protected]', '*********');
INSERT 0 1

# SELECT change_account_email('[email protected]', '[email protected]');
 change_account_email 
----------------------
                    1
(1 row)

# SELECT * FROM accounts;
 a_id | a_first_name | a_last_name |     a_email     | a_password 
------+--------------+-------------+-----------------+------------
    1 | John         | Doe         | [email protected] | *********
(1 row)

Here is SQLFiddle demo

edited Dec 11, 2013 at 4:43

answered Dec 11, 2013 at 4:29

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

user3015561 Over a year ago

Okay. Sorry, I am new here. Thank you again!

Collectives™ on Stack Overflow

I'm creating a stored procedure in PostgreSQL, but I get an error 'syntax error at or near "UPDATE"'

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related