Re: How to lock and unlock table in postgresql

Hi Alban,

Thanks for the reply.

1) I'm using PostgreSQL 8.1; So, I can't use RETURNING clause!
2) The function I gave is just to put my understanding! Thanks for spotting the error though.

Regards,
Siva.

-----Original Message-----
From: Alban Hertroys [mailto:haramrae(at)gmail(dot)com]
Sent: Thursday, November 17, 2011 1:20 PM
To: Siva Palanisamy
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to lock and unlock table in postgresql

On 17 Nov 2011, at 7:10, Siva Palanisamy wrote:

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

As John says, you're re-inventing the wheel that sequences solve. You could also get the id using INSERT .. RETURNING.

You have another problem on your hands though. You have a naming conflict between your variable name and a column name in that second query: id and Id are the same.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

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

-----------------------------------------------------------------------------------------------------------------------