Re: [GENERAL] mysql replace in postgreSQL?

[Kostas Maistrelis]

blackwater dev wrote:

In MySQL, I can use the replace statement which either updates the
data there or inserts it.  Is there a comporable syntax to use in
postgreSQL?
I need to do an insert and don't want to have to worry about if the
data is already there or not...so don't want to see if it there, if so
do update if not insert...etc.
 


look this functions..
is not general solution..


CREATE TYPE mydata AS (
   f1 integer ,
   f2 integer,
);



CREATE OR REPLACE FUNCTION updatefoo(data  mydata, myid bigint) RETURNS 
boolean AS $$
DECLARE
BEGIN
   update foo_table  set
   f1 = mydata.f1,
   f2 = mydata.f2
   WHERE id  = myid;

    IF NOT FOUND THEN
        return false;
    END IF;         
    return true;
END
$$ LANGUAGE plpgsql;




CREATE OR REPLACE FUNCTION insertfoo(data  mydata, myid bigint) RETURNS 
boolean AS $$
DECLARE
   rep boolean DEFAULT false;
BEGIN
   insert into foo_table (
    id , 
    f1,
    f2   
   ) values (
     mydata.id,
     mydata.f1,
     mydata.f2
   );

 return rep;
END
$$ LANGUAGE plpgsql;




CREATE OR REPLACE FUNCTION replaceFoo(data  mydata, myid bigint) RETURNS 
boolean AS $$
DECLARE
rep boolean = false;
BEGIN
   rep = updatefoo(mydata,myid );
   if not rep then
       rep =  insertfoo(mydata,myid );
   end if;
   return rep;
END
$$ LANGUAGE plpgsql;


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend