simply want to swap all column value we can achieve by,
1) renaming column
2) by creating one more temporary column
if we like to swap column say fname and lname to be swapped by condition on id > 300 on table personal
CREATE OR REPLACE FUNCTION swap() RETURNS integer
AS
$BODY$
DECLARE
-- declare anyvariable over here
VFname varchar;
VLasswd varchar ;
VSwap personal%rowtype;
-- create cursor
CSwap cursor for select id,fname,lname from personal where id > 300;
BEGIN
open CSwap;
loop fetch next from CSwap into VSwap;
exit when not found;
raise notice 'fname : % , lname : %',VSwap.fname,VSwap.lname;
update personal set fname=VSwap.lname,lname=VSwap.fname where id = VSwap.id;
end loop;
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql'
call the function by
select swap();
explain : cursor CSwap hold the value of fname, lanme and id greater then 300
when looping cursor just updating fname and lanme using cursor variable VSwap.lanme and VSwap.fname by matching id with VSwap.id
