CODEWITHSUNDEEP
javascriptpostgresqlperlplv8
Curtis
Curtis

Reputation: 2704

Converting basic JS to Perl

I'm currently trying to convert a PostgreSQL function from JS to Perl since the PLV8 extension isn't available in my environment.

The original JS/PLV8 code I have is:

CREATE OR REPLACE FUNCTION plays_users_stats_trigger()
  RETURNS trigger AS $$

    if (TG_OP === 'UPDATE' && OLD.user_id !== NEW.user_id)
      throw new Error('Update of user_id not allowed');

    var userId, gross = 0, net = 0, num = 0;
    var bet, cashOut, bonus;

    // Add new values.
    if (NEW) {
      userId  = NEW.user_id;
      bet     = NEW.bet;
      bonus   = NEW.bonus || 0;
      cashOut = NEW.cash_out || 0;

      gross  += Math.max(cashOut - bet, 0) + bonus;
      net    += (cashOut - bet) + bonus;
      num    += 1;
    }

    // Subtract old values
    if (OLD) {
      userId  = OLD.user_id;
      bet     = OLD.bet;
      bonus   = OLD.bonus || 0;
      cashOut = OLD.cash_out || 0;

      gross  -= Math.max(cashOut - bet, 0) + bonus;
      net    -= (cashOut - bet) + bonus;
      num    -= 1;
    }

    var sql =
      'UPDATE users ' +
      '  SET gross_profit = gross_profit + $1, ' +
      '      net_profit   = net_profit   + $2, ' +
      '      games_played = games_played + $3 ' +
      '  WHERE id = $4';
    var par = [gross,net,num,userId];
    plv8.execute(sql,par);
$$ LANGUAGE plv8;

However being a novice to Perl I'm struggling to get the functionality right, the current code I've got is:

CREATE OR REPLACE FUNCTION plays_users_stats_trigger() RETURNS trigger AS $$
    if($_TD->{event} = 'UPDATE' && $_TD->{old}{i}->user_id != $_TD->{new}{i}->user_id) {
        die('Update of user_id not allowed: %, at %',$_TD,now());
    }

    my $userId = undef;
    my $gross = 0; 
    my $net = 0; 
    my $num = 0;
    my $bet = undef;
    my $cashOut = undef;
    my $bonus = undef;

    if($OLD) {
        $userId = $OLD->user_id;
        $bet = $OLD->bet;
        $bonus = $OLD->bonus || 0;
        $cashOut = $OLD->cash_out || 0;
        $gross -= max($cashOut - $bet, 0) + $bonus;
        $net -= ($cashOut - $bet) + $bonus;
        $num -= 1;
    } elsif($NEW) {
        $userId = $NEW->user_id;
        $bet = $NEW->bet;
        $bonus = $NEW->bonus || 0;
        $cashOut = $NEW->cash_out || 0;
        $gross += max($cashOut - $bet, 0) + $bonus;
        $net += ($cashOut - $bet) + $bonus;
        $num += 1;
    }

    $sql = 'UPDATE users SET gross_profit = gross_profit + $1, net_profit = net_profit + $2, games_played = games_played + $3 WHERE id = $4';
    spi_exec_prepared($sql, [$gross, $net, $num, $userId]);

$$ LANGUAGE plperl;

Upvotes: 0

Views: 266

Answers (1)

Håkon Hægland
Håkon Hægland

Reputation: 40748

I am not familiar with PostgreSQL, but looking at your code sample, here are some comments:

  • $TG_OP = 'UPDATE' : string comparison in Perl is done with the eq operator
  • $OLD.user_id !== $NEW.user_id : numerical comparison for non-equality should be done using the != operator
  • null should probably be undef.
  • $userId = OLD.user_id : it seems like $OLD is an object (by looking at your code)? Then you should probably access the user_id attribute using something like $OLD->user_id. The same goes for all other attribute accesses using the dot . operator in Javascript; those should probably be a -> operator in Perl.
  • NOTICE 'Update of user_id not allowed: %, at %' : you can throw an exception in Perl using the die function
  • IF ... ELSE : In Perl this is if ... else
  • Math.max($cashOut - $bet, 0) : In Perl you can use max from the module List::Util

Upvotes: 1

Related Questions