Question: Updating a time-stamp field in a PostgreSQL function using LOCALTIMESTAMP fails

Question

Updating a time-stamp field in a PostgreSQL function using LOCALTIMESTAMP fails

Answers 0
Added at 2016-12-02 16:12
Tags
Question

When I call this line outside of a Postgresql function both the status and last_updated feilds are updated.

UPDATE data.listings_applications SET status = 'new', last_updated = localtimestamp WHERE token=28632;

but when I do the same inside a function only the status is updated! Does the localstamp function only works outside postgresql functions ?

CREATE OR REPLACE FUNCTION update_application_status_by_token (applicationtoken integer, userid integer, newstatus data.applicationstatus) RETURNS boolean
    LANGUAGE plpgsql
AS $$
    DECLARE
      applicationId INTEGER;
      currentStatus data.applicationstatus;
    BEGIN
      IF newStatus IS NULL OR newStatus='new'
        THEN RETURN FALSE;
      ELSE
        SELECT id,status INTO applicationId,currentStatus FROM data.listings_applications WHERE token = applicationToken;
        IF currentStatus = newStatus THEN
          RETURN FALSE;
        ELSE
          INSERT INTO data.listings_applications_status(application_id, user_id, status)  VALUES (applicationId, userId, newStatus);
          UPDATE data.listings_applications SET status = newStatus, last_updated = localtimestamp WHERE token=applicationToken;
          RETURN TRUE;
        END IF;
      END IF;
    END;
$$;

I call it like that and it returns true but the last_updated feild doesnt get updated!

SELECT update_application_status_by_token(724422366, 58296, 'prescreen');
Answers
Source Show
◀ Wstecz