Received: from opal.albourne.com (opal.albourne.com [195.212.241.228]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3PFPn357780 for ; Wed, 25 Apr 2001 11:25:49 -0400 (EDT) (envelope-from alessio@albourne.com) Received: from emily.cy.albourne.com (emily.cy.albourne.com [192.168.51.32]) by opal.albourne.com (8.9.3/8.9.3/Albourne/R/1.5) with ESMTP id SAA05292; Wed, 25 Apr 2001 18:25:45 +0300 Received: from albourne.com (alessio@madeleine.cy.albourne.com [192.168.51.75]) by emily.cy.albourne.com (8.9.3/8.9.3/Albourne/S/1.4) with ESMTP id SAA16258; Wed, 25 Apr 2001 18:25:40 +0300 (EET DST) Message-ID: <3AE6EC74.5F770259@albourne.com> Date: Wed, 25 Apr 2001 18:25:40 +0300 From: Alessio Bragadini Organization: APL Financial Services Overseas Ltd. X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.2.18pre21 i686) X-Accept-Language: it, en MIME-Version: 1.0 To: Thomas Swan CC: pgsql-sql@postgresql.org Subject: Re: MySql 'REPLACE' References: <5.1.0.14.0.20010425082557.00ae1830@tangent.ics.olemiss.edu> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200104/376 X-Sequence-Number: 2734 Thomas Swan wrote: > You should be able to do this with two separate queries inside a > transaction. Yes, sorry, I was not clear enough. Unfortunately, the function I need to write is a "generic" one that takes a number of fields/values and generate a SQL instruction. So, there is no previous information about the underlining table structure. What I did is a query to pg_class and other pg_* tables to get a list of unique indexes and build the DELETEs needed prior the INSERT. > The only part I'm not clear on is whether to use an 'and' or an 'or' > on the delete. Check and see if all values have to match or if > partial matches, i.e. only one of the columns, are acceptable. If it > does partial matching, then use the 'or', otherwise use the 'and'. I went for AND. You're welcome to check if my (Perl) code is completely wrong :-) This is the MySql version: sub sqlReplace { my($self, $table, $data) = @_; my($names, $values); foreach (keys %$data) { if (/^-/) { $values .= "\n $data->{$_},"; s/^-//; } else { $values .= "\n " . $self->{_dbh}->quote($data->{$_}) . ','; } $names .= "$_,"; } chop($names); chop($values); my $sql = "REPLACE INTO $table ($names) VALUES($values)\n"; $self->sqlConnect(); return $self->sqlDo($sql) or errorLog($sql); } and my PostgreSQL version: sub sqlReplace { my($self, $table, $data) = @_; my($names, $values); foreach (keys %$data) { if (/^-/) { $values .= "\n $data->{$_},"; s/^-//; } else { $values .= "\n " . $self->{_dbh}->quote($data->{$_}) . ','; } $names .= "$_,"; } chop($names); chop($values); # We study the table structure - this code comes from psql -E my $cols = $self->{_dbh}->selectcol_arrayref (q{SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = ? AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum}, undef, $table) || []; unshift @$cols, ''; # To have values starting at index 1 my $all_uniq = $self->{_dbh}->selectcol_arrayref (q{SELECT indkey FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = ? AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND indisunique IS TRUE}, undef, $table) || []; $self->{_dbh}->{AutoCommit} = 0; # BEGIN TRANSACTION foreach (@$all_uniq) { my @acols = @$cols[split]; my $check = 1; map {$check &&= defined $data->{$_}} @acols; next unless $check; my $sql = "DELETE FROM $table WHERE " . join (' AND ', map "$_ = " . $self->{_dbh}->quote($data->{$_}), @acols); $self->{_dbh}->do ($sql); } my $sql = "INSERT INTO $table ($names) VALUES ($values)"; $self->{_dbh}->do($sql); $self->{_dbh}->commit; # END TRANSACTION # return $self->sqlDo($sql) or errorLog($sql); } -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925