conditional INSERTs
От | David Wright |
---|---|
Тема | conditional INSERTs |
Дата | |
Msg-id | Pine.LNX.4.33.0107181454230.28112-100000@merleau.rprc.washington.edu обсуждение исходный текст |
Список | 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?
В списке pgsql-general по дате отправления: