This is the text of a select procedure I have written to replace a
view. It works - so I can continue doing this in this manner,
particularly if this how I'm "supposed" to do it. But it seems a little
clumsy, and I don't know how this compiles compared to a different
implementation.

Given a table "addresss", with a foreign key zipcode_id in the table
zipcode, and the function get_zipinfo(), which returns a zipcode, city,
and state based on an id -

CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
CHARACTER (2), ZIPCODE CHARACTER (10))
AS
DECLARE VARIABLE zipcode_id INTEGER;
BEGIN
FOR SELECT address.id, address, street, street_type, suite, zipcode_id
FROM address, street_types
WHERE address.id = :address_id and street_type_id = street_types.id
INTO :id, :address, :street, :street_type, :suite, :zipcode_id
DO BEGIN
FOR SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id)
INTO :city, :state, :zipcode
DO
SUSPEND;
END
END !!

The other way I tried (but could not get sucessfully created) was -

CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
CHARACTER (2), ZIPCODE CHARACTER (10))
AS
BEGIN
FOR SELECT address.id, address, street, street_type, suite,
(SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id) )
FROM address, street_types
WHERE address.id = :address_id and street_type_id = street_types.id
INTO :id, :address, :street, :street_type, :suite, :city, :state,
:zipcode
DO
SUSPEND;
END !!

I kept getting an error on the first comma in the second select -
(SELECT city,

Dunno why - syntax SEEMS reasonable . . .

Daniel