CODEWITHSUNDEEP
postgresqlstored-procedures
fLen
fLen

Reputation: 598

PostgreSQL: If Else Statement for Insert, Delete and Update in One Function

How I can convert this SP/Function from MySQL to PostgreSQL?

 DELIMITER $$
CREATE DEFINER=`user_name`@`%` PROCEDURE `sp_TABLE_name`(
pTransType int,
pId bigint,
pName varchar(250),
pStartFrom datetime,
pStartTo datetime,
pSignature longblob)
BEGIN

if pTransType = 1 then

    insert into TABLE_name (Id, Name, startfrom, startto, signature)    
       values(pId, pName, pStartFrom, pStartTo, pSignature);

end if;

if pTransType = 2 then

    update TABLE_name set 
            Name = pName,
            startfrom = pStartFrom,
            startto = pStartTo,
            signature  = pSignature
        where Id = pId;

end if;

if pTransType = 3 then

    delete from TABLE_name where id = pId;

end if;

END$$
DELIMITER ;

I tried Case when Statement but, other error shows.. Is there any other way to do it?

Upvotes: 1

Views: 2789

Answers (1)

Hambone
Hambone

Reputation: 16387

I think it would look like this:

CREATE OR REPLACE FUNCTION sp_TABLE_name(pTransType int, pId bigint, pName varchar(250),
   pStartFrom timestamp, pStartTo timestamp, pSignature bytea)
  RETURNS void AS
$BODY$
BEGIN
  if pTransType = 1 then

      insert into TABLE_name (Id, Name, startfrom, startto, signature)    
         values(pId, pName, pStartFrom, pStartTo, pSignature);

  elsif pTransType = 2 then

      update TABLE_name set 
              Name = pName,
              startfrom = pStartFrom,
              startto = pStartTo,
              signature  = pSignature
          where Id = pId;

  elsif pTransType = 3 then

      delete from TABLE_name where id = pId;

  end if;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Note I did have to conform your datatypes to PostgreSQL equivalents (or my best guess of what they would be).

Upvotes: 1

Related Questions