The following function returns this error:
pg_query(): Query failed: ERROR: permission denied for relation customers
CONTEXT: PL/pgSQL function "newprofile" line 8 at SQL statement
What is wrong here?
CREATE SEQUENCE CustomerID;
CREATE TABLE Customers
(
CustomerID INTEGER NOT NULL DEFAULT nextval('CustomerID'),
IsActive BOOLEAN NOT NULL DEFAULT TRUE,
Email VARCHAR(64) NOT NULL CHECK (Email = substring(Email from
'^.+@.+\..+$')),
Password VARCHAR(15) NOT NULL CHECK (Password = substring(Password from
'^[0-9_A-Za-z]{5,15}$')),
FullName VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
Phone VARCHAR(15) NOT NULL,
Created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
Accessed TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (CustomerID),
UNIQUE (Email)
);
CREATE FUNCTION NewProfile(VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR)
RETURNS INTEGER AS '
DECLARE
aEmail ALIAS FOR $1;
aPassword ALIAS FOR $2;
aName ALIAS FOR $3;
aAddr ALIAS FOR $4;
aPhone ALIAS FOR $5;
BEGIN
INSERT INTO Customers(Email, Password, FullName, Address, Phone)
VALUES(lower(aEmail), aPassword, aName, aAddr, aPhone);
RETURN currval(''CustomerID'');
END;
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION NewProfile(VARCHAR, VARCHAR, VARCHAR, VARCHAR,
VARCHAR) TO SomeCustomer;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
