How to compatible postgresql with MySQL if function

postgresql compatible with MySQL if function

if function description

In mysql, the usage of if() function is similar to the three-dimensional expression in java. It has many uses. The specific syntax is as follows:

IF(expr1,expr2,expr3), if the value of expr1 is true, the value of expr2 is returned. If the value of expr1 is false, the value of expr3 is returned.

Postgresql custom if function compatibility

create or replace function if(bln boolean,inValue1 anyelement,inValue2 anyelement)
returns anyelement as
$$
begin
if bln=true then
   return inValue1;
else
   return inValue2;
end if;
end;
$$
language plpgsql;

create or replace function if(bln boolean,inValue1 numeric,inValue2 numeric)
returns numeric as
$$
begin
if bln=true then
   return inValue1;
else
   return inValue2;
end if;
end;
$$
language plpgsql;

create or replace function if(bln boolean,inValue1 numeric,inValue2 text)
returns text as
$$
begin
if bln=true then
   return inValue1;
else
   return inValue2;
end if;
end;
$$
language plpgsql;

MySQL, Oracle, Postgresql compatible and adaptable

The difference between using sql

1. dual table

The unique table of oracle is to limit the complete structure of SQL statements

select (select * from table_name where age = 20) t from dual

MySQL and pgsql do not have this table, so you can directly remove it

select (select * from table_name where age = 20) t

2. Boolean type

oracle and mysql do not have boolean types, you can use number(int) or char instead.

There is bool type in pgsql, and numbers and characters are automatically converted to boolean types (0→f, 1→t, no→f, yes→t)

3. Update table alias

pgsql is not applicable, mysql and oracle support

update table_name t set  = 'abc' where id = 1

4. Passing the value of string

pgsql and oracle only support single quotes

select * from table_name where name = 'abc'

MySQL single quotes/double quotes are supported

select * from table_name where name = "abc"

5. Batch Insert

MySQL, pgSQL batch insertion

insert into table_name() values()

Oracle batch insertion

insert all into table_name() values()

Mybatis is compatible with different databases

Use the if tag to determine _databaseId, and adapt to different databases respectively. The specific code is as follows:

<insert  parameterType="">
    <if test="_databaseId=='mysql' or _databaseId=='postgresql'">
        insert into table_name 
        (<include ref></include>)
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (<include ref></include>)
        </foreach>
    </if>
    <if test="_databaseId=='oracle'">
        insert all
        <foreach collection="list" item="item" index="index" separator="">
            into table_name 
            (<include ref></include>)
            values (<include ref></include>)
        </foreach>
        select * from dual
    </if>
</insert>
 
<sql >
    id,name,age,gender
</sql>
<sql >
    #{,jdbcType=VARCHAR}, #{,jdbcType=VARCHAR}, 
    #{,jdbcType=INTEGER},#{,jdbcType=INTEGER}
</sql>

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.