Thread: Help : Update and insert record based on several value in the parameter
Hi,
I want to create a function to update my table (flag='9') and insert new record (flag='0') with the rfidnumber specified in a parameter.
This parameter may have several value seperated by space (ie. 11 22 33 44)
CREATE OR REPLACE FUNCTION public.fcreate_rfid (
znumber varchar
)
RETURNS boolean AS
$body$
BEGIN
--update old record which has the same rfid number and flag='0' if exists
update tblrfid set flag='9' where flag='0' and rfidnumber in (znumber);
-- generate new record
insert into tblrfid(tanggal, flag, rfidnumber)
select localtimestamp, '0', regexp_split_to_table(znumber, ' ');
return true;
END;
$body$
LANGUAGE 'plpgsql';
when i called this function using command :
select fcreate_rfid('11 22 33 44');
This function fail to update the old record, but success to insert the new record.
Please help me how to fixed this problem. I know the problem is the update command, but i don't know the correct it. Googling anywhere didn't find any solution.
Thank you
Re: Help : Update and insert record based on several value in the parameter
From
Andrew Gierth
Date:
>>>>> "Hengky" == Hengky Lie <hengkyliwandouw@gmail.com> writes: Hengky> Hi, Hengky> I want to create a function to update my table (flag='9') and Hengky> insert new record (flag='0') with the rfidnumber specified in a Hengky> parameter. rfidnumber is stored as text/varchar? if it's something else, change the suggestions below accordingly. Hengky> This parameter may have several value seperated by space (ie. Hengky> 11 22 33 44) update ... and rfidnumber = any (string_to_array(znumber,' ')) Or, do the split just once: DECLARE z_ids text[] := string_to_array(znumber, ' '); BEGIN update ... where flag='0' and rfidnumber =any (z_ids); insert into ... select localtimestamp, '0', id from unnest(z_ids) as u(id); -- Andrew (irc:RhodiumToad)
Re: Help : Update and insert record based on several value in the parameter
From
Pavel Stehule
Date:
út 29. 1. 2019 v 13:50 odesílatel Hengky Lie <hengkyliwandouw@gmail.com> napsal:
Hi,I want to create a function to update my table (flag='9') and insert new record (flag='0') with the rfidnumber specified in a parameter.This parameter may have several value seperated by space (ie. 11 22 33 44)
For this kind of parameters, PostgreSQL has a array type - for example a int[] can be perfect for this purpose
If you still use a this format, then you can use a string_to_array function and searching in array
so your UPDATE command should to look like
UPDATE tblrfid SET flag = 9 WHERE flag = '0' and rfidnumber = ANY(string_to_array(znumber))
Regards
Pavel
CREATE OR REPLACE FUNCTION public.fcreate_rfid (znumber varchar)RETURNS boolean AS$body$BEGIN--update old record which has the same rfid number and flag='0' if existsupdate tblrfid set flag='9' where flag='0' and rfidnumber in (znumber);-- generate new recordinsert into tblrfid(tanggal, flag, rfidnumber)select localtimestamp, '0', regexp_split_to_table(znumber, ' ');return true;END;$body$LANGUAGE 'plpgsql';when i called this function using command :select fcreate_rfid('11 22 33 44');This function fail to update the old record, but success to insert the new record.Please help me how to fixed this problem. I know the problem is the update command, but i don't know the correct it. Googling anywhere didn't find any solution.Thank you
I don't know what you are using as a front end to call this update, but I have been finding json/jsonb parameters passed into a function / procedure and then using postgres json functions to process the data as a good solution.
If your data fits postgres array datatype, as others have mentioned, that is also a good (and possibly simpler) solution.
On Tue, 29 Jan 2019 at 14:50, Hengky Lie <hengkyliwandouw@gmail.com> wrote:
Hi,I want to create a function to update my table (flag='9') and insert new record (flag='0') with the rfidnumber specified in a parameter.This parameter may have several value seperated by space (ie. 11 22 33 44)CREATE OR REPLACE FUNCTION public.fcreate_rfid (znumber varchar)RETURNS boolean AS$body$BEGIN--update old record which has the same rfid number and flag='0' if existsupdate tblrfid set flag='9' where flag='0' and rfidnumber in (znumber);-- generate new recordinsert into tblrfid(tanggal, flag, rfidnumber)select localtimestamp, '0', regexp_split_to_table(znumber, ' ');return true;END;$body$LANGUAGE 'plpgsql';when i called this function using command :select fcreate_rfid('11 22 33 44');This function fail to update the old record, but success to insert the new record.Please help me how to fixed this problem. I know the problem is the update command, but i don't know the correct it. Googling anywhere didn't find any solution.Thank you