PostgreSQL Snippets - Revision history

Busk at 00:24, 3 April 2022

2022-04-03T00:24:40Z

Busk at 00:12, 3 April 2022

2022-04-03T00:12:50Z

Busk: Created page with "``` # Update modified_date column on update CREATE OR REPLACE FUNCTION update_modified_date_column() RETURNS TRIGGER AS $$ BEGIN NEW.modified_date = now(); RETURN N..."

2019-07-27T00:30:24Z

Created page with "``` # Update modified_date column on update CREATE OR REPLACE FUNCTION update_modified_date_column() RETURNS TRIGGER AS $$ BEGIN NEW.modified_date = now(); RETURN N..."

New page

```
# Update modified_date column on update
CREATE OR REPLACE FUNCTION update_modified_date_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified_date = now();
RETURN NEW;
END;
$$ language 'plpgsql';

or even better but more costly compute if only if content has changed

CREATE OR REPLACE FUNCTION update_modified_date_column()
RETURNS TRIGGER AS $$
BEGIN
IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
NEW.modified_date = now();
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$$ language 'plpgsql';

BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE update_modified_date_column();
```

@@ Line 1: / Line 1: @@
 # Update update_at when table specified at bottom is updated.
 ```
 SELECT 'up SQL query';

@@ Line 1: / Line 1: @@
 ```
-# Update modified_date column on update
+SELECT 'up SQL query';
-CREATE OR REPLACE FUNCTION update_modified_date_column()
+# Update updated_at column on update
 RETURNS TRIGGER AS $$
 BEGIN
-     NEW.modified_date = now();
+     NEW.updated_at = now();
      RETURN NEW;
 END;
 $$ language 'plpgsql';
@@ Line 11: / Line 13: @@
 or even better but more costly compute if only if content has changed
-CREATE OR REPLACE FUNCTION update_modified_date_column()
+CREATE OR REPLACE FUNCTION update_updated_at_column()
 RETURNS TRIGGER AS $$
 BEGIN
     IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
-       NEW.modified_date = now();
+       NEW.updated_at = now();
        RETURN NEW;
     ELSE
@@ Line 23: / Line 25: @@
 $$ language 'plpgsql';
+BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE  update_updated_at_column();
 ```