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
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
"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)
ú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
Show quoted text
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
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#m_-6825970932945715657_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
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:
Show quoted text
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
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#m_5923011125801819668_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>