From: | David Wright <ichbin(at)heidegger(dot)rprc(dot)washington(dot)edu> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | conditional INSERTs |
Date: | 2001-07-18 22:13:22 |
Message-ID: | Pine.LNX.4.33.0107181454230.28112-100000@merleau.rprc.washington.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Lamar Owen | 2001-07-18 22:16:27 | Re: psql -l |
Previous Message | Tom Lane | 2001-07-18 22:10:26 | Re: Errors in logs |