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.
-----------------------------------------------------------------------------------------------------------------------
