0

Hi I am having trouble creating a function for postgres SQL, my script is below.

Just to explain: The person_details table's primary key is just id, the person_id is a different field in the same person_details table.

The idea is to call the function and use it as so (for multiple emails and id numbers) some emails and ids being searched for are from the same account so that is what the DISTINCT is for:

EXECUTE delete_accounts(('[email protected]','[email protected]'),(1231342321,1224235343))

The below is giving out the error:

ERROR: syntax error at or near "user" SQL state: 42601 Character: 840

I know there is some issues around calling the table user in the database but UPDATE 'user' gives the same error - i don't think this is my problem. Apologies if there is other errors in this - I am used to mySQL and this is first time using postgres SQL

If it helps - I am using this in pgadmin3

CREATE OR REPLACE FUNCTION delete_accounts(emailList TEXT, personID BIGINT)

 RETURNS INTEGER AS $$

DECLARE
  personDetailsID BIGINT;
  userIDlist BIGINT;
  appID TEXT;

BEGIN
  --find all person_details ids using the person_id or email given
  personDetailsID = (SELECT DISTINCT id FROM person_details WHERE email IN $1 OR person_id IN $2);

  --set the register_status in the people found in personDetailsID to closed 
  UPDATE person_details AS p 
    SET p.onboarding_status = 'CLOSED' 
      WHERE p.id IN personDetailsID;

  --Each person set up can have multiple users - find the user ids using the user_person_details linking table
  userIDlist = (SELECT upd.user_id FROM user_person_details AS upd WHERE upd.person_details_id IN personDetailsID);

  --update the enabled field for the user to 'f'
  UPDATE user as u
    SET u.enabled = 'f' 
      WHERE u.id in userIDlist;

$$ LANGUAGE plpgsql;

1 Answer 1

1

The problem is that user is a reserved keyword in SQL (see here for a list).

If you really need to use it as an object name, you have to double quote it ("user") so that it is recognized as an identifier rather than a keyword.

But it's best to avoid such identifiers.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.