Hi all. I'm interested in doing an insertion on a set of fields
where, while none are unique, the combination of the fields is unique. If
they exist already, I want to return the row ID. If there are already any
solutions that do this please let me know.
Also, in trying to solve this, I tried creating a server-side
function (in either PL/pgSQL or PgPerl).
I have a client-side perl function which already does this, which
works fine on the client side, but I'd like to move this across to the
server side. The function takes two parameters, the table name, and a
hash of the column names paired with the values I want to optionally
insert. The guts of the perl function are:
-----
(@result) = $dbh->selectrow_array("SELECT ID FROM $table WHERE $selectmap");
if($#result < 0) {
$dbh->do("
INSERT INTO $table
($keystr)
VALUES ($valstr)
");
(@result) = $dbh->selectrow_array("SELECT ID FROM $table WHERE $selectmap");
}
return(@result);
-----
I also have to construct $selectmap, $keystr, and $valstr earlier
in the function. The main problem I'm running into at the moment is
passing in a variable number of parameters for the different field/value
combinations. Does anyone have any ideas about how to get around this?
Incidentally, if someone is interested in working on a more
general solution, someone came up with a list of similar problems at the
following link:
http://archive.xmldb.org/xupdate-dev/2001/Dec/msg00001.html
Thanks all,
:)
--
Tim Nelson
Systems Administrator
Sunet Internet
Tel: +61 3 5241 1155
Fax: +61 3 5241 6187
Web: http://www.sunet.com.au/
Email: sysadmin@sunet.com.au
