pl/pgsql loop thru columns names

From: Dilyan Berkovski <berkovski(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pl/pgsql loop thru columns names
Date: 2009-08-19 06:33:50
Message-ID: 89890.56271.qm@web33105.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,

I have a nasty table with many repeating columns of the kind port_ts_{i}_<something>, where {i} is from 0 to 31, and <something> could be 3 different words.
I have made a pl/pgsql function that checks those columns from port_ts_1_status to port_ts_31_status and counts something, however this is nasty and ugly thing to do - repeat 31 times one statement for looping thru {i} and do it 3 times to loop thru <something>. This is how I do it now:
REATE OR REPLACE FUNCTION auto_util()
RETURNS "trigger" AS
$BODY$DECLARE
count_free integer;
util real;
BEGIN
count_free = 0;
IF new.port_ts_1_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_2_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_3_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_4_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_5_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_6_status='free' THEN count_free = count_free + 1;
End if;
IF new.port_ts_7_status='free' THEN count_free = count_free + 1;
End if;
.... and till the end ilke this.

Can I do a loop, and make the column naming to be build dynamically (as the dynamic queries in pl/pgsql)?
something like
for i in 1..31 do
Loop
If new.port_ts_[i]_status='free' ... and so on
end Loop.
I tryed but unsuccessfully :(, and can not find much in the documentation.
I am using 8.1 and 8.2 Postgresql DBs.
Someone with an idea?
Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2009-08-19 07:03:13 Re: pl/pgsql loop thru columns names
Previous Message Scott Marlowe 2009-08-19 06:01:13 question about /etc/init.d/postgresql in PGDG