Hi Myles,

>> In order to improve performance on a query, I would like to add a trigger to
>> a table to automatically update a 'disply' field with some values. The
>> values would look like this:
>>
>> 'XX,YY,ZZ'
>>
>> And are based on the number of related resources that are allocated to a
>> slot. My table structure looks kinda like this:
>>
>> <ALLOCATION> 1 --- N <RESOURCES>
>>
>> (but it’s a bit more complex in real life). Each resource has Initials, and
>> this is what I want to show in the XX, YY, ZZ field. In order to do this, I
>> believe I will need a small stored procedure that can be called from within
>> a trigger, that will select all of the allocated resources, and then loop
>> through the rows returned, extracting the Initials and creating a string
>> variable, seperated by commas for display.
>>
>> I can see the syntax for a WHILE loop in PSQL, however how do you associate
>> this with the number of rows returned from a SELECT statement? I need to
>> loop through each row returned and add to the string variable I'm
>> constructing.
>>
>> All pointers greatly appreciated.
>
> Not bullet-proof, but the following might get you onto the right track.
>
> A selectable SP for getting a list of resource initials for a given
> allocation.
>
> SET TERM ^^ ;
> CREATE PROCEDURE P_RESOURCELIST (
> ALLOCATION_ID Integer)
> returns (
> RES_LIST VarChar(100))
> AS
> declare variable vInitial varchar(5);
> begin
> res_list = '';
> for select initial from resources where allocation_id =
> :allocation_id into :vInitial do
> begin
> if (res_list = '') then
> begin
> res_list = vInitial;
> end else
> begin
> res_list = res_list || ',' || vInitial;
> end
> end
> if (res_list = '') then
> begin
> res_list = null;
> end
> suspend;
> end
> ^^
> SET TERM ; ^^

Btw, this SP isn't necessary with FB 2.1 anymore, because there is a
built-in LIST function which does exactly this. For example:

select list(initial, ',') from resources where allocation_id = 1


--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com