At 06:18 PM 17/12/2003 -0600, you wrote:

>I have two databases,

tables?

>one a database of pets and another a database of
>owners

You can't do correlated queries across the boundaries of a database.

So, let's assume a typo, and pets and owners are tables within one database.

>each pet has its own unique petid and each owner has its own unique
>ownerid, multiple pets may have the same ownerid (ie owned by the same
>owner), i have been trying to figure out how to implement a stored
>procedure or udf that is aggregated so that i can take the results of
>"select petname from pets where ownerid = :OwnerID", that the results
>are as a comma seperated listm ie instead of returning an editable
>resultset such as
>fluffy
>fifi
>fubar
>i get a list such as
>fluffy, fifi, fubar
>I have been fiddling with stored procedures but as far as i can tell the
>only way to implement this would be in a udf, but i wold just like a
>conformation of this fact, and if this is true,

Not true; but you do need to protect the output string from exceeding the
limit for a varchar - that is 32,765 bytes - a lot fewer *characters* if
you are using a character set.

>can someone point me in
>the direction of some documentation that would be usable?

The IB 6 beta docs do have some material. The "Using Firebird" manual on
the IB Phoenix CD has a whole chapter on PSQL. You could also Google for
"InterBase" + "Stored Procedures".

Anyway, limits apart, here is how to generate your string:

create procedure list_pets (OwnerID integer)
returns (pet_list varchar(1024) as
declare variable current_pet varchar(40) /* size of the col in the table */
declare variable num_pets smallint;
declare variable plural char;
begin
/* initialise the string */
pet_list = '*';
num_pets = 0;
plural = ''; /* empty string */
for select pet_name from pets
where OwnerID = :OwnerID
into :current_pet do
begin
if (current_pet is not null) then
begin
num_pets = num_pets + 1;
if (num_pets > 1) then
plural = 's';
if (pet_list <> '*') then
begin
pet_list = pet_list || ', ' || current_pet;
end else
pet_list = current_pet;
end
end
if (num_pets = 0) then
pet_list = 'Owner ' || :OwnerID || ' has no pets';
else
pet_list = num_pets || ' pet' || plural || ': ' || pet_list;
suspend; /* only if you want to SELECT from this procedure */
end

/heLen