I am looking for a possibility to insert a new row into a table and return the serial of the pk - or if the row already exists only return its serial number. Mysql has a insert-parameter "ignore" that circumvents a possible conflict with a unique-constraint so that one can simply insert and then select the inserted ID. With postgresql I couldn't find a similar way.
One solution is to write a stored procedure that does this job. Then I have to specify each parameter of the table-columns and can determine whether a row is new or not.
The disadvantage of this approach is, that the function gets table-speciffic and I have to write a function for every table where this check has to be done. I also have to maintain the perl-code and that of the procedures - since there are many of these tables, it doesn't make a sense.
My question is if there is a more general solution - like the ignore-statement. I couldn't imagine that noone else has had this problem before
Maybe it is possible to parse two parameters to the function, one including the search string and one string that includes all parameters. Then the function has to split this string and to create the insert-statement on the fly - sounds a little bit complicated.
Certainly the solution is much easier and I just didn't find the appropriate page in the manual - if someone could give me a hint ?