Postgres RowType field Names

Question

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

Can you please edit your question and add some sample (PL/pgSQL) code how you pass or declare the variable? And how the output should look like? — user330315
– user330315, Commented Jan 10, 2021 at 11:47
I dunno why in the workbench i'm getting the values and in the PLSQL I'm getting null — vvekselva
– vvekselva, Commented Jan 10, 2021 at 14:49
You might want to read Is adding the ‘tbl’ prefix to table names really a problem? — user330315
– user330315, Commented Jan 10, 2021 at 15:44

user330315 · Accepted Answer · 2021-01-10 15:41:32Z

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;

Collectives™ on Stack Overflow

Postgres RowType field Names

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related