1

Considering our company had no real relational schema in MySQL and, in the process of trying to transform our schema, we've run into some limitations using MySQL, we decided to import tables and data over to PostgreSQL. We've already done that. One thing I can't figure out (besides not being able to find decent tutorials on PostgreSQL 10 that aren't the documentation), is how to have variables, or if I even need variables for the following situation.

We have a subscribers table. This has a primary key subscriber_id. We have a subscriber_info table, that has a foreign key to the subscriber_id in subscribers. When we delete a subscriber, we just set the active record in the subscribers table to 0. We want this to propagate so that setting the master subscribers record to 0 updates all the references to that subscriber_id and sets their active records to 0. Similarly, if I restore a subscription record's active flag to 1, I want that to propagate as well.

So, I have two tables:

subscribers
------------
subscriber_id (PK)
active

subscriber_info
----------------
subscriber_info_id (PK)
subscriber_id      (FK)
subscriber_name 
active
datetime_created   (current_timestamp)
datetime_modified  (current_timestamp on update current_timestamp)

The following trigger works in MySQL:

DELIMITER $$
CREATE TRIGGER delete_or_restore_subscriber AFTER UPDATE ON subscribers
FOR EACH ROW BEGIN
  SET @subscriber_id = old.subscriber_id;
  IF (new.active <=> 0) THEN
    UPDATE subscriber_info
    SET subscriber_info.active = 0
    WHERE subscriber_info.subscriber_id = @subscriber_id;
  ELSEIF (new.active <=> 1) THEN
    UPDATE subscriber_info
    SET subscriber_info.active = 1
    WHERE last_created_record = (
      SELECT MAX(subscriber_info.datetime_created)
      FROM subscriber_info
      WHERE subscriber_info.subscriber_id = @subscriber_id
      AND subscriber_info.active = 0
    );
  END IF;
END$$
DELIMITER ;

But in Postgres SET appears to be a configuration setter.

Is there a way to re-write this trigger? Is there a decent tutorial on triggers? I'd like some way to verify or test my trigger before actually executing it. Any other books, videos or anything would go a long way in understanding this.

Alex
  • 2,145
  • 6
  • 36
  • 72
  • https://dba.stackexchange.com/questions/75142/postgresql-equivalent-of-mysql-query-variables?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Mitch Wheat May 21 '18 at 01:19
  • @MitchWheat Thanks for this. I guess I didn't really know what to google to be honest since most of the time "MySQL SET in PostgreSQL" just returns the documentation for SET in PostgreSQL. Should I close this question? Or delete it? – Alex May 21 '18 at 01:21
  • It's entirely up to you. Leaving might increase the surface area for people searching. It's marked as a possible duplicate so you could just leave it here if you want to. – Mitch Wheat May 21 '18 at 01:25
  • shoot, I closed it too soon it seems. I'm trying to use multiple variables using `WITH` and am getting syntax errors when I try to define more than one, ie. `WITH vars as (SELECT something as id WHERE something.else = 0,SELECT another_thing as cool WHERE cool.other = 0)` gives me: `Syntax error at or near ","` – Alex May 21 '18 at 01:57

0 Answers0