Difference between revisions of "PostgreSQL Snippets"
Jump to navigation
Jump to search
(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...") |
|||
| Line 1: | Line 1: | ||
| + | # Update update_at when table specified at bottom is updated. | ||
``` | ``` | ||
| − | # Update | + | SELECT 'up SQL query'; |
| − | CREATE OR REPLACE FUNCTION | + | # Update updated_at column on update |
| + | CREATE OR REPLACE FUNCTION update_updated_at_column() | ||
RETURNS TRIGGER AS $$ | RETURNS TRIGGER AS $$ | ||
BEGIN | BEGIN | ||
| − | NEW. | + | NEW.updated_at = now(); |
| − | RETURN NEW; | + | RETURN NEW; |
END; | END; | ||
$$ language 'plpgsql'; | $$ language 'plpgsql'; | ||
| Line 11: | Line 13: | ||
or even better but more costly compute if only if content has changed | or even better but more costly compute if only if content has changed | ||
| − | CREATE OR REPLACE FUNCTION | + | CREATE OR REPLACE FUNCTION update_updated_at_column() |
RETURNS TRIGGER AS $$ | RETURNS TRIGGER AS $$ | ||
BEGIN | BEGIN | ||
IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN | IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN | ||
| − | NEW. | + | NEW.updated_at = now(); |
RETURN NEW; | RETURN NEW; | ||
ELSE | ELSE | ||
| Line 23: | Line 25: | ||
$$ language 'plpgsql'; | $$ language 'plpgsql'; | ||
| − | + | BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column(); | |
| − | BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE | ||
``` | ``` | ||
Revision as of 00:12, 3 April 2022
Update update_at when table specified at bottom is updated.
SELECT 'up SQL query';
# Update updated_at column on update
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ language 'plpgsql';
or even better but more costly compute if only if content has changed
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
NEW.updated_at = now();
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$$ language 'plpgsql';
BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();