| 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: | Whole Thread | Raw Message | 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,
| 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 |