Обсуждение: How to implement transaction in plpgsql?

Dear all,

How to implement transaction in plpgsql?
I have try to use it but fail. And I don't know the reason.
Here is my function...

CREATE FUNCTION sp_templatetouser(INTEGER, INTEGER) RETURNS INTEGER AS '
 DECLARE
  puserid ALIAS FOR $1;
  pplateid ALIAS FOR $2;
  rec_affected INTEGER;
 BEGIN

  BEGIN WORK;
  DELETE FROM userrights WHERE userid = puserid;
  INSERT INTO userrights SELECT puserid, rightid, allow FROM platedetails
WHERE plateid = pplateid;

  GET DIAGNOSTICS rec_affected = ROW_COUNT;
  IF rec_affected = 3 THEN
   RETURN 1;
  ELSE
   ROLLBACK WORK;
   RETURN 0;
  END IF;
  COMMIT WORK;
 END;
' LANGUAGE 'plpgsql';

best regards,
Corn.

"Corn" <corn@tryit.com> writes:

> Dear all,
>
> How to implement transaction in plpgsql?
> I have try to use it but fail. And I don't know the reason.
> Here is my function...

You can't use transactions inside a function.  The reason is that when
a function executes you are already inside a transaction (every SQL
statement is a transaction if there is no explicit BEGIN) and PG
doesn't support nested transactions.

See the FAQ for more info; I'm pretty sure this issue is in there (if
not, it should be).

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

"Corn" wrote:
  >Dear all,
  >
  >How to implement transaction in plpgsql?
  >I have try to use it but fail. And I don't know the reason.

Check the documentation again.  It is not supported.  (There are no
nested transactions in PostgreSQL.)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But without faith it is impossible to please him; for
      he that cometh to God must believe that he is, and
      that he is a rewarder of them that diligently seek
      him."        Hebrews 11:6

If I remember correctly, the document in "function"
section sas that people can not use transaction
statements (begin, commit, rollback) IN any function.
Instead, we should use these transaction statements
OUTSIDE function.

Good luck.

CN

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com