I need to check whether a particular row exists in a postgresql database
and, if it does not, INSERT it. Furthermore, if it does exist, I want to
get its key for use in other SQL statements. I would like to do this in
the most portable way possible, i.e. a series of pure SQL statements or,
baring that, a Trigger, rather than within a Perl or C program.
That pretty much sums up the problem, but in case background helps, here
it is. I have an XML file detailing methods and their arguments
<method name="foo" type="void">
<argument name="order" type="int"/>
</method>
<method name="bar" type="float">
<argument name="order" type="int"/>
<argument name="x" type="float"/>
</method>
The database model consists of three tables:
methods: mKey mName mType
arguments: aKey aName aType
methods_arguments: mKey aKey
Now, in many, cases, the same <argument> appears in multiple <method>s. I
don't want to have to have multiple, identical rows in the arguments
table, hence the problem of a conditional insert posed above. I am writing
an XSL script to process the XML file, so it's a lot easier to output pure
SQL than, e.g. a Perl program.
If I do find an existing row in the arguments table for an argument, I
need its aKey in order to be able to insert a new row into the
methods_arguments table linking it to the method at hand. So the flow I
want to implement is
if EXISTS ( SELECT * FROM arguments
WHERE mName = $mName AND mType = $mType ) {
$mKey = arguemnts.aKey
} else {
INSERT INTO arguments ( mName , mType ) VALUES ( $mName , $mType )
$mKey = currval(mKey)
}
INSERT INTO methods_arguments ( mKey , aKey ) = ( $mKey , $aKey )
Can someone suggest a way to do this?
