Thread: Problem compiling function with BEGIN WORK; COMMIT WORK;

Hi,

I need to use the BEGIN WORK; and COMMIT WORK; to lock a table when I'am doing a SELECT and UPDATE operation.

The code is not compiling, the error is:

[error]ERROR:  syntax error at or near "work" at character 1
QUERY:  work
CONTEXT:  SQL statement in PL/PgSQL function "apr_apanhar_ownership_email" near line 7
 [/error]

And the code is:

[code]
CREATE OR REPLACE FUNCTION "public"."apr_apanhar_ownership_email" (ppid int4, out ppid_email_envio int4) RETURNS integer AS
$body$
DECLARE
    pPID                 alias for $1;
    vID_EMAIL_ENVIO            int4;
   
    BEGIN

        begin work;
        lock table atem_emails_envios in access exclusive mode;

        select id_email_envio from atem_emails_envios
        where dat_sended is null
        and (i_started is null or i_started < (current_timestamp - '2 hours'::interval))
        and (pid is null or pid = pPID)
        order by dat_inserted asc
        limit 1
        into vID_EMAIL_ENVIO;

        update atem_emails_envios
        set
        i_started = current_timestamp,
        pid = pPID
        where id_email_envio = vID_EMAIL_ENVIO;
        commit work;
       
        ppid_email_envio := vID_EMAIL_ENVIO;
   
    END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
[/code]

What is wrong here? Can someone give me a clue.

Best Regards,

On 25/04/2010 9:07 AM, Andre Lopes wrote:
> Hi,
>
> I need to use the BEGIN WORK; and COMMIT WORK; to lock a table when I'am
> doing a SELECT and UPDATE operation.

PostgreSQL's server-side functions do *not* support transaction
management. They're functions that're used inside an existing transaction.

However, if you do not explcitly BEGIN a transaction before calling your
function, the statement your function runs in will start and stop its
own transaction. In other words, these two things are equivalent:

BEGIN;
SELECT my_function();
COMMIT;

and

SELECT my_function();

(outside an existing transaction)



Because your function is *always* inside a transaction, it can always
acquire locks and the like. It doesn't need to explicitly start a
transaction first.

--
Craig Ringer