Published 4/30/2007 1:45:05 AM  in  Postgresql

Lets create a single procedure for updating (if row exist) or insert if it doesn't.

When you need expressions and program flow you need to turn to PL/pgSQL. You might have seen we have in the end of the functions we created ended it up with a 'language sql' statement - now we are basically gonna end with LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION insertorupdate_city(p_in_city_id bigint, 
	p_in_cityname character varying(50),
	p_in_country_id int)
  RETURNS BIGINT AS
$$
BEGIN
	If $1 = -1 THEN
		insert into city(city,country_id) values($2,$3);
		return currval('city_city_id_seq') ;
	ELSE 
		update city set city=$2 where city_id=$1;
		return $1;
	END IF;
END
$$
  LANGUAGE 'plpgsql';


 

 

and C# code:

        public long UpdateOrInsert(long city_id, string newcity, long country_id)
        {
            Npgsql.NpgsqlConnection oConn = new Npgsql.NpgsqlConnection("Server=192.168.10.4;Port=5432;Userid=postgres;Password=stefan;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable;Database=pagila");
            oConn.Open();


            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand("insertorupdate_city(:city_id, :city, :country_id)", oConn);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new Npgsql.NpgsqlParameter("city_id", DbType.Int16));
            command.Parameters.Add(new Npgsql.NpgsqlParameter("city", DbType.String, 50));
            command.Parameters.Add(new Npgsql.NpgsqlParameter("country_id", DbType.Int16));
            command.Parameters[0].Value = city_id;
            command.Parameters[1].Value = newcity;
            command.Parameters[2].Value = country_id;

            //command.ExecuteNonQuery();
            long lNewId = (long)command.ExecuteScalar();
            //long lNewId = -1;

            oConn.Close();
            return lNewId;

        }