Re: MySql 'REPLACE'
От | Alessio Bragadini |
---|---|
Тема | Re: MySql 'REPLACE' |
Дата | |
Msg-id | 3AE6EC74.5F770259@albourne.com обсуждение исходный текст |
Ответ на | MySql 'REPLACE' (Alessio Bragadini <alessio@albourne.com>) |
Ответы |
use of arrow keys to traverse history
|
Список | pgsql-sql |
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 1my $all_uniq = $self->{_dbh}->selectcol_arrayref (q{SELECT indkey FROMpg_class c, pg_class c2, pg_index i WHERE c.relname = ? AND c.oid = i.indrelid AND i.indexrelid = c2.oidAND indisunique IS TRUE}, undef, $table) || [];$self->{_dbh}->{AutoCommit} = 0; # BEGIN TRANSACTIONforeach (@$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