CODEWITHSUNDEEP
postgresql
vvekselva
vvekselva

Reputation: 823

Postgres RowType field Names

I want to list the all the field Names (Column Names) in the rowtype variable in postgres. Is there are anyways to accomplish it.

create  or replace function purchase_payment_merchant_count() returns void as $$
DECLARE
    currentrow   operational.tbl_purchase_payments%rowtype;

BEGIN

    RAISE WARNING 'Trigger Function Called';
    raise WARNING 'Count %',(select count(*) as Counts from (select sum(pending_amount) as pending_amount, fk_merchant as fk_merchant
     from operational.tbl_purchase_payments where fk_payment_status = 2 group by fk_merchant) counts);
    FOR currentrow IN 
    select sum(pending_amount), fk_merchant
     from operational.tbl_purchase_payments where fk_payment_status = 2 group by fk_merchant
     
         
       loop
       
       
       RAISE WARNING 'Current ROw %', currentrow;
       RAISE WARNING 'Pending Amount: % and fk_merchant: %',currentrow.pending_amount,currentrow.fk_merchant;
       update operational.tbl_merchant_accounts_payable
                set pending_payment = currentrow.pending_amount
                where fk_merchant = currentrow.fk_merchant;
       END LOOP;
            
END;
$$ LANGUAGE plpgsql;

Logs:

2021-01-10 16:08:10.294 IST [2325]  WARNING:  Current ROw (3404,1,,,,,)
2021-01-10 16:08:10.294 IST [2325]  CONTEXT:  PL/pgSQL function purchase_payment_merchant_count() line 15 at RAISE
    SQL statement "SELECT purchase_payment_merchant_count()"
    PL/pgSQL function purchase_payment_merchant_metric() line 4 at PERFORM
2021-01-10 16:08:10.295 IST [2325]  WARNING:  Pending Amount: <NULL> and fk_merchant: <NULL>

Output from sql work bench:

sum fk_merchant
3404.25 1

Upvotes: 0

Views: 253

Answers (1)

user330315
user330315

Reputation:

The definition of currentrow as a rowtype of tbl_purchase_payments only makes sense if you retrieve all columns from tbl_purchase_payments in your SELECT statement - which you don't seem to do.

You haven't shown us the definition of ``, but most likely, the reason why the record's fields are null, is that pending_amount and fk_merchant are not the first two columns in that table. If you don't populate all of them, they are populated from left to right (in the order as listed in the CREATE TABLE statement).

For a "dynamic" row variable, just declare it as record:

create  or replace function purchase_payment_merchant_count() returns void as $$
DECLARE
  currentrow   record;
BEGIN

  RAISE WARNING 'Trigger Function Called';
  raise WARNING 'Count %',(select count(*) as Counts from (select sum(pending_amount) as pending_amount, fk_merchant as fk_merchant
  from operational.tbl_purchase_payments where fk_payment_status = 2 group by fk_merchant) counts);
  
  FOR currentrow IN 
    select sum(pending_amount) as pending_amount, fk_merchant
    from operational.tbl_purchase_payments 
    where fk_payment_status = 2 
    group by fk_merchant
  loop
    RAISE WARNING 'Current ROw %', currentrow;
    RAISE WARNING 'Pending Amount: % and fk_merchant: %',currentrow.pending_amount,currentrow.fk_merchant;
    
    update operational.tbl_merchant_accounts_payable
      set pending_payment = currentrow.pending_amount
    where fk_merchant = currentrow.fk_merchant;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

But this can be done without a slow, inefficient and non-scalable row-by-row processing in a loop. You can do this with a single UPDATE statement:

update operational.tbl_merchant_accounts_payable pay
  set pending_payment = t.amount
from (
  select sum(pending_amount) as amount, fk_merchant
  from operational.tbl_purchase_payments 
  where fk_payment_status = 2 
  group by fk_merchant
) t                
where pay.fk_merchant = t.fk_merchant;

Upvotes: 1

Related Questions