Problem compiling function with BEGIN WORK; COMMIT WORK;

From: Andre Lopes <lopes80andre(at)gmail(dot)com>
To: postgresql Forums <pgsql-general(at)postgresql(dot)org>
Subject: Problem compiling function with BEGIN WORK; COMMIT WORK;
Date: 2010-04-25 01:07:29
Message-ID: g2q18f98e681004241807z35e9d2a8mcaa7bfc6cf8c0860@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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,

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2010-04-25 02:11:51 Re: how to set CACHEDEBUG ?
Previous Message Bruce Momjian 2010-04-25 01:01:39 Re: Identical command-line command will not work with \i metacommand and filename