pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alessio Bragadini <alessio@albourne.com>
To: Thomas Swan <tswan@ics.olemiss.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: MySql 'REPLACE'
Date: Wed, 25 Apr 2001 18:25:40 +0300
Message-ID: <3AE6EC74.5F770259@albourne.com> (raw)
References: <5.1.0.14.0.20010425082557.00ae1830@tangent.ics.olemiss.edu>

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



view thread (6+ messages)  latest in thread

Message-ID: <3AE6EC74.5F770259@albourne.com>
Permalink:  ../3AE6EC74.5F770259@albourne.com/
Also on:    postgresql.org/message-id/3AE6EC74.5F770259@albourne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: alessio@albourne.com, tswan@ics.olemiss.edu
  Subject: Re: MySql 'REPLACE'
  In-Reply-To: <3AE6EC74.5F770259@albourne.com>
  References: <5.1.0.14.0.20010425082557.00ae1830@tangent.ics.olemiss.edu> <3AE6EC74.5F770259@albourne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox