conditional INSERTs

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?

Browse pgsql-general by date

  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