Hi ya,
I've 2 tables: One being the master (table1) and another being the slave (table2). I want to lock table1 until the below function completes, and it should unlock the same at last.
Below is my function. Pls guide me on how to apply locking table1 and unlocking the same finally. The scenario why I require this is: I want to add 70000 records in these tables. As I want to retrieve the last generated Id of table1 which is needed for a foreign column in table2, I used MAX operation. If anyone tries to add a record manually, I suspect it might get disturbed. So, I wish to go for locking and unlocking the table for every record insertion such that other process waits till this function completes its tasks. If there is a better solution, kindly let me know.
CREATE OR REPLACE FUNCTION Fun()
RETURNS VOID AS '
DECLARE
Id INTEGER;
BEGIN
INSERT INTO table1 VALUES (DEFAULT, ''Sample'', DEFAULT);
SELECT MAX(id) INTO Id FROM table1;
INSERT INTO table2 VALUES(DEFAULT,Id,''sampledata'');
END;
' LANGUAGE 'plpgsql';
Regards,
Siva.
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.
-----------------------------------------------------------------------------------------------------------------------
