将MySQL存储过程转换为PostgreSQL函数。我如何处理这些replace（）调用？

I am just about finished converting 84 stored procedures from MySQL to PostgreSQL functions. The only thing that has not been figured out is what to do with the replace() calls. The function doesn't exist in PostgreSQL. I am sure someone has had to do this before. Any suggestions?

我刚刚将84个存储过程从MySQL转换为PostgreSQL函数。唯一没有弄清楚的是如何处理replace（）调用。 PostgreSQL中不存在该函数。我相信有人不得不这样做。有什么建议么？

set @sql="CREATE TABLE `tbodefcall...(An UnGodly Amount of code that creates tables ...honeid_cd_idx (PhoneID,CallDate);";
    set @sql =  Replace(@sql, 'tbodefcallback', concat('tbo_callback',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefcontact', concat('tbo_contact',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefscriptkvota', concat('tbo_scriptkvota',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefscript', concat('tbo_script',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodeftermcode', concat('tbo_termcode',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefprojectlogon', concat('tbo_projectlogon',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefcomment', concat('tbo_comment',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefvoicerecorder', concat('tbo_voicerecorder',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefquestiongroup', concat('tbo_questiongroup',_ProjectID) );
    set @sql =  Replace(@sql, 'tbodefquestion', concat('tbo_question',_ProjectID) );

Thanks for any help or suggestions. You guys have rocked in helping me through this project!

感谢您的任何帮助或建议。你们一直在帮助我完成这个项目！

2 个解决方案

#1

Postgresql does have the replace function, at least in 8.4; which version are you using?

Postgresql确实有替换功能，至少在8.4;你使用的是哪个版本？

steve@steve@[local] =# select replace('create table tblfoo', 'tblfoo', 'tblfoo_44');
        replace         
------------------------
 create table tblfoo_44
(1 row)

#2

You're actually looking for an "upsert" command. In PostgreSQL you perform it the following way:

你实际上在寻找一个“upsert”命令。在PostgreSQL中，您可以通过以下方式执行它：

UPDATE ...;
IF NOT FOUND THEN -- UPDATE didn't touch anything
  INSERT ...;
END IF;

#1