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...") |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | # Update update_at when table specified at bottom is updated. | ||
``` | ``` | ||
| − | + | CREATE OR REPLACE FUNCTION update_updated_at_column() | |
| − | CREATE OR REPLACE FUNCTION | ||
RETURNS TRIGGER AS $$ | RETURNS TRIGGER AS $$ | ||
BEGIN | BEGIN | ||
| − | NEW. | + | IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN |
| − | RETURN NEW; | + | NEW.updated_at = now(); |
| + | RETURN NEW; | ||
| + | ELSE | ||
| + | RETURN OLD; | ||
| + | END IF; | ||
| + | END; | ||
| + | $$ language 'plpgsql'; | ||
| + | |||
| + | CREATE TRIGGER mytable_update_at_ts | ||
| + | BEFORE UPDATE ON urls FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column(); | ||
| + | ``` | ||
| + | |||
| + | |||
| + | ``` | ||
| + | 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; | END; | ||
$$ language 'plpgsql'; | $$ language 'plpgsql'; | ||
| Line 11: | Line 31: | ||
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 43: | ||
$$ 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 | ||
``` | ``` | ||
Latest revision as of 00:24, 3 April 2022
Update update_at when table specified at bottom is updated.
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';
CREATE TRIGGER mytable_update_at_ts
BEFORE UPDATE ON urls FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
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();