Re: mysql replace in postgreSQL?

[Kostas Maistrelis <maisk@xxxxxxxxxxxxxxx>]

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