0

We're having an issue writing a trigger function that involves 3 tables: CustomerProfile, DropdownStats and OrderDetails. A simplified schema of each is as follows...

CustomerProfile table...this table has a row for all of the column names of the OrderDetails table.

CustID ColumnName  IsDropDown   
------ ----------  ----------  
1      firstcolumn false  
1      ...  
1      orderid     false  
1      format      false
1      department  false  
1      ...  
1      lastcolumn  false  

DropdownStats table...

OrderID ColumnName ColumnValues CountOfColumnValues
------- ---------- ------------ -------------------  
1       format     circle       15  
1       format     square       23  
1       format     triangle     3  
2       format     circle       10  
3       format     square       5  
3       format     triangle     10  

OrderDetails table...

firstcolumn ... orderid format department ... lastcolumn  
----------- --- ------- ------ ---------- --- ----------  
data        dta 1       circle 123        dta data  
data        dta 1       square 134        dta data  
...  
data        dta 2       circle 354        dta data  
...  

The trigger is written for the CustomerProfile table as an AFTER UPDATE trigger.

If the 4th row in the CustomerProfile table (where columnname = format) transitions the IsDropDown from false to true, then we have a to ADD rows to the DropdownStats table as they are pulled from the OrderDetails table.

The trigger function as written is producing results in the DropdownStats table like below but we want results like the sample above.

DropdownStats table...

OrderID ColumnName ColumnValues CountOfColumnValues
------- ---------- ------------ -------------------
1       format     format       41
2       format     format       10
3       format     format       15

The problem is having the trigger function insert a static column name and then have a separate row for each of the distinct values found for that column and a count of rows that hold those values. Here is one (of several) attempt at this...the problem is the two new.columnname They should not be the same.

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    insert into dropdownstats 
    (orderid,columnname,columnvalues,countofcolumnvalues) 
      select distinct
      orderid
      , new.columnname -- we want this to be the static actual name of the column, repeated for every record inserted (Ex: 'format')
      , new.columnname -- we want a row for each of the values found for the column in the database (Ex: 'circle', 'square', 'triangle', etc.)
      , count(new.columnname) -- the count of the records found for the value (Ex: for 'circle, count was 15)
      from orderdetails
      group by orderid, new.columnname; 
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

If I just use the query tool and manually create and run the select statement shown below, I get what I want.

select distinct orderid, 'format', format, count(format) from orderdetails group by orderid, format;

But how do we translate the manual query into an equivalent trigger function? How do we distinguish the two "new.columnname" from each other?

bumped to the homepage by Community 3 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • 1
    How often does the structure of OrderDetails change? – RDFozz Dec 14 '17 at 16:21
  • It does not change. It is very stable – JoeDirt Dec 14 '17 at 16:47
  • That makes the first of my two suggestions more reasonable (see below). Any time you'd add a new column to OrderDetails, you'd have to update the trigger with an INSERT using that specific column. – RDFozz Dec 14 '17 at 18:19
0

Your problem is that your query is using new.columnname to mean different things.

In the SELECT list of the subquery, the first new.columnname is intended to use the actual value stored new.columnname. The second new.columnname, the one in the COUNT() and the one in the GROUP BY are all intended not to be the literal value stored in new.columnname, but a reference to the actual OrderDetails column named in new.columnname. In other words, the first is the string literal "format", while the second is the database column OrderDetails.format.

Unfortunately, the engine will view each of new.columnname in the query as a string value "format", and will never interpret it as OrderDetails.format.

There are at least two potential ways to get around this problem:

Explicit INSERTs for each column

For each of the columns in OrderDetails, write a separate INSERT statement in your trigger:

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    if new.columnname = 'first_column' then
      insert into dropdownstats 
      (orderid,columnname,columnvalues,countofcolumnvalues) 
        select 
        orderid
        , 'first_column'
        , first_column
        , count(*)
        from orderdetails
        group by orderid, first_column; 
    elsif new.columnname = 'format' then
      insert into dropdownstats 
      (orderid,columnname,columnvalues,countofcolumnvalues) 
        select 
        orderid
        , 'format'
        , format
        , count(*)
        from orderdetails
        group by orderid, format; 
    elsif ...
    end;
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

(If there's special handling you want to do if new.columnname doesn't match any of the values, you can use CASE ... WHEN ... ELSE ... END; instead of IF ... THEN ... ELSIF ... THEN ... END;, if you like.)

Dynamic SQL

Unless PostgreSQL has some restriction against it, you could use dynamic SQL to build the SQL statement you need each time through:

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    EXECUTE format( 'insert into dropdownstats '
                    '(orderid,columnname,columnvalues,countofcolumnvalues) '
                      'select '
                      'orderid '
                      ', %L '
                      ', %I '
                      ', count(*) '
                      'from orderdetails '
                      'group by orderid, %I '
                   ,new.columnname, new.columnname, new.columnname);
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

CAVEATS: Code is untested, and PostgreSQL is not my primary SQL language, so I may have included typos or misunderstood some subtleties.

  • The Dynamic SQL looks like it will work better for us. The reason being that the first one was made using my abbreviated table structure. In actuality, there are 150+ columns on the table and it isn't practical to use the if-else structure. Of course you had no idea of that. I'm going to give the Dynamic a whirl and see if it can be pulled off in Postgres. Thank you! – JoeDirt Dec 14 '17 at 18:38
  • It's much easier to maintain, too. However, some people have a knee-jerk reaction to the phrase "dynamic SQL" (and, as I noted, I'm not really a PostgreSQL user, so it's possible that limits on what can happen in triggers might prevent this from working). Happy to try to help if you run into a snag, if I can. – RDFozz Dec 14 '17 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.