PostgreSQL error when deleting an attribute within a product node

A quick search hasn't shown me what to do in Postgres for handling multiple tables in a DELETE statement. That's how MySQL wants it, but it's probably not the SQL Standard way to do it.

From the Postgres DELETE documentation:

Do not repeat the target table in the usinglist, unless you wish to set up a self-join.

This sounds like a different use of the USING clause than what MySQL does in its DELETE statements. I'm not sure how I can reconcile using the target table in USING for MySQL and not using it for Postgres.

Might be that I should just run two queries and make it simpler.

This is the syntax that you want.

db_query("DELETE FROM $opt_table WHERE EXISTS (
SELECT * FROM {uc_attribute_options} AS ao
WHERE $opt_table.oid = ao.oid
AND $opt_table.$id = $sql_type
AND ao.aid IN (%s))", $values);

Can I use the deleted table's alias in the subquery?

db_query("DELETE FROM {uc_product_options} AS po WHERE EXISTS (
  SELECT * FROM {uc_attribute_options} AS ao
  WHERE po.oid = ao.oid
    AND po.$id = $sql_type
    AND ao.aid IN (%s))", $values);

Actually, that syntax doesn't seem to work at all.

Nope, you can't alias the table you're deleting from ... but you can use the full name.

db_query("DELETE FROM {uc_product_options} WHERE EXISTS (
  SELECT * FROM {uc_attribute_options} AS ao
  WHERE {uc_product_options}.oid = ao.oid
  AND ao.aid IN (%s))
  AND {uc_product_options}.$id = $sql_type", $values);

I think that should do it.

Not using aliases makes me grumpy, but that does work as far as I can tell. Somebody make sure these patches work for both Drupal 5 and 6 on Postgres.

I can confirm that multi_delete-d6.patch works with ubercart 6.x-2.0-beta3 on PostgreSQL.

Good enough for me. Committed multi_delete-d6.patch.

Anybody using Drupal 5, Ubercart, and Postgres?

I can also confirm that multi_delete-d5.patch works with ubercart 5.x-1.7 on PostgreSQL 8.3.

Excellent!