[Zope] Can I capture results from insert in Z SQL methods?

1 Nov 2002

      In a Z SQL method I want to create a row in a table which has several
foreign keys.  The method is going to create other rows in subsidiary tables
with some of its inputs, then use their primary keys as foreign keys in the
final insert.  For example, a contact table has foreign keys referring to
address and phone tables.  The code looks something like

    begin;
    # voice phone
    insert into phone (
      ...
    );
    # fax
    insert into phone (
      ...
    );
    insert into address (
        ...
    );
    insert into contact (
        name,
        email,
        address_id,
        phone,
        fax
      )
      values (
        ...
    );
    commit;

In the final insert I need row ids corresponding to rows I inserted in the
phone and address tables.  Can I capture them as a result returned from the
earlier inserts or do I need to do a subselect in the last insert, like so:

    insert into contact (
        name,
        email,
        address_id,
        phone,
        fax
      )
      values (
        <dtml-sqlvar name type="string">,
        <dtml-sqlvar email type="string">,
        (select max(id) from address
          where address1 = <dtml-sqlvar address1 type="string">
            and address2 = <dtml-sqlvar address2 type="string">
            and city = <dtml-sqlvar city type="string">
            and state = <dtml-sqlvar state type="string">
            and postal_code = <dtml-sqlvar postal_code type="string">),
        (select max(id) from phone
          where phone_area = <dtml-sqlvar phone_area type="string">
            and phone_number = <dtml-sqlvar phone_number type="string">
            and type = 'work'),
        (select max(id) from phone
          where fax_area = <dtml-sqlvar fax_area type="string">
            and fax_number = <dtml-sqlvar fax_number type="string">
            and type = 'fax')
    );

or do Z SQL methods support some other mechanism?  I didn't see any examples
of such stuff in the Zope Book.

(I'm using the Psycopg DA, Zope 2.5.1.)

Thanks,

-- 
Skip Montanaro - skip@pobox.com
http://www.mojam.com/
http://www.musi-cal.com/