https://supabase.com/ logo
Join DiscordCommunities
Powered by
I am getting this error from a database function: ...
# help
m
I am getting this error from a database function: 
type of parameter 1 (record) does not match that when preparing the plan (db_transactions)
For the code, context and details, check the thread. I would really appreciate any sort of help.
n
Hello @Muezz! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the 
/title
command! We have solved your problem? Click the button below to archive it.
m
Code:
Copy code
sql
CREATE OR REPLACE FUNCTION update_transaction (t_row record)
  RETURNS record AS $$
    DECLARE
      last_debit_balance NUMERIC;
      last_credit_balance NUMERIC;
    BEGIN

      raise log 'TESTING 2 --> T_ROW: %', t_row;

      SELECT COALESCE(db_transactions.deb_acc_bal,0)
      INTO last_debit_balance
      FROM public.db_transactions
      WHERE (db_transactions.id != t_row.id AND (db_transactions.deb_acc = t_row.deb_acc OR db_transactions.cred_acc = t_row.deb_acc))
      ORDER BY db_transactions.t_date DESC, db_transactions.updated_at DESC
      FETCH FIRST ROW ONLY;

      raise log 'TESTING 3 --> last_debit_balance: %', last_debit_balance;

      SELECT COALESCE(cred_acc_bal,0)
      INTO last_credit_balance
      FROM db_transactions
      WHERE (db_transactions.id != t_row.id AND (cred_acc = t_row.cred_acc OR deb_acc = t_row.cred_acc))
      ORDER BY t_date DESC, updated_at DESC
      FETCH FIRST ROW ONLY;

      raise log 'TESTING 4 --> last_credit_balance: %', last_credit_balance;

      -- Here the new balances of both accounts are calculated and updated.
      t_row.deb_acc_bal = last_debit_balance - t_row.amount;
      t_row.cred_acc_bal = last_credit_balance + t_row.amount;

      RETURN t_row;
    END;
  $$ LANGUAGE plpgsql security definer;
Copy code
sql
CREATE OR REPLACE FUNCTION fnc_update_balances ()
  RETURNS trigger AS $$
    DECLARE
        r RECORD;
        updated_t_row RECORD;
        last_debit_balance NUMERIC;
        last_credit_balance NUMERIC;
    BEGIN

        raise log 'TESTING 1 --> NEW: %', NEW;

        updated_t_row = update_transaction(NEW);

        raise log 'TESTING 5 --> UPDATED NEW: %', updated_t_row;
        
        UPDATE PUBLIC.db_transactions
        SET deb_acc_bal = updated_t_row.deb_acc_bal,
            cred_acc_bal = updated_t_row.cred_acc_bal
        WHERE id = updated_t_row.id;

        FOR r IN (SELECT * 
                  FROM db_transactions
                  WHERE t_date > NEW.t_date AND (deb_acc = NEW.deb_acc OR cred_acc = NEW.cred_acc)
                  ORDER BY t_date ASC ,updated_at ASC)
        loop 
            updated_t_row = update_transaction(r);
            UPDATE PUBLIC.db_transactions
            SET deb_acc_bal = updated_t_row.deb_acc_bal,
                cred_acc_bal = updated_t_row.deb_acc_bal
            WHERE id = updated_t_row.id;

            raise log 'TESTING n --> Loop Ran for: %', r.id;

        END loop;
        
        RETURN NEW;
    END;
  $$ LANGUAGE plpgsql security definer;
Copy code
sql
-- CREATE TRIGGER tr_update_balances
-- AFTER INSERT ON public.db_transactions
-- FOR EACH ROW EXECUTE PROCEDURE fnc_update_balances();
Context: User can add financial transactions. The table has fields such as date, amount, debit account, debit account balance, credit account, credit account balance, etc. From the client-side app, the balance fields come as 
NULL
. The database function called 
fnc_update_balances()
is triggered AFTER the new transaction is inserted. This function calls another function called 
update_transaction(t_row record)
. This second function looks for the last transaction in the table, gets the debit and credit balance of the respective account and returns the same row from the argument with the updated balance fields. In the case where the user adds an old transaction, all balance fields of all rows/transactions beyond that date end up having incorrect values. The next FOR loop looks for any rows that have dates greater than the date of the current transaction that has been just added and updated. If there are none, the transaction is the latest and for loop shouldnt run. If there are, the same function 
update_transaction(t_row record)
is called for each row later than the current transaction.
Error: If I add the latest transaction, it is added just fine WITH the updated balances that obviously depend on the last transaction in the table. However, when I add an old transaction, I get this error and no transaction is added or updated. 
type of parameter 1 (record) does not match that when preparing the plan (db_transactions)
Primary Question: What is causing this and how can I resolve this? Google hasn't really given me anything concrete but my gut says that I am using the variable type 
record
in an incorrect way somewhere. Secondary Question: Now that you know what I am trying to achieve with this database function, is there a better, more efficient or more concise way to do this?
c
It seems that your RECORD variables are always rows from the db_transactions table - in that case, try replacing all RECORD types with db_transactions%rowtype (you can read more about it here: https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES)
n
wubba_lubba_whatever (2022-04-28)
c
RECORD has some limitation on the contexts in which it can be used (I am afraid I don't fully understand them myself) so you are probably right that the usage of RECORD is the issue - if that is the case, using row types should fix it
(I cannot comment on the secondary question because I don't fully understand your use case)
m
Thanks for responding. I will try that out and see if that works. As for the secondary question, I can give you more context and/or explanation on what I am trying to achieve if you can spare a few mins for me.
I am getting this: 
syntax error at or near "%"
t
I think %Rowtype night be optional. Have you tried without it?
m
Yes it works without 
%rowtype
just fine. It is not working the way I want it to but it is working. Seems like a logic problem on my part. I'll try to fix that too.
PreviousNext
Powered by