Обсуждение: Weird error message in SQL function call

Hi all.

I've got this error message while running an SQL function:

---- BEGIN
psql:filtb.sql:1029: ERROR:  COMMIT is not allowed in a SQL function
CONTEXT:  SQL function "f_relazione" during startup
funzione SQL "f_relazione" istruzione 1
----  END

Of course, I have no COMMIT command either in the function itself or
in any other function called by f_relazione().
In the syslog I see that this is labelled as error #0A000 "Feature not
supported".

Is there any hint?

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

Vincenzo Romano wrote:
> Hi all.
>
> I've got this error message while running an SQL function:
>
> ---- BEGIN
> psql:filtb.sql:1029: ERROR:  COMMIT is not allowed in a SQL function
> CONTEXT:  SQL function "f_relazione" during startup
> funzione SQL "f_relazione" istruzione 1
> ----  END
>
> Of course, I have no COMMIT command either in the function itself or
> in any other function called by f_relazione().

Seeing as you are reading in a large .sql file, I'd look for a quoting
error - if the closing quote for the function-body isn't correct it'll
run on, and then encounter a COMMIT further down the file.

--
   Richard Huxton
   Archonet Ltd

On Monday 16 July 2007 12:18:23 Richard Huxton wrote:
> Vincenzo Romano wrote:
> > Hi all.
> >
> > I've got this error message while running an SQL function:
> >
> > ---- BEGIN
> > psql:filtb.sql:1029: ERROR:  COMMIT is not allowed in a SQL
> > function CONTEXT:  SQL function "f_relazione" during startup
> > funzione SQL "f_relazione" istruzione 1
> > ----  END
> >
> > Of course, I have no COMMIT command either in the function itself
> > or in any other function called by f_relazione().
>
> Seeing as you are reading in a large .sql file, I'd look for a
> quoting error - if the closing quote for the function-body isn't
> correct it'll run on, and then encounter a COMMIT further down the
> file.

You hit the spot!

The actual problem was in a deeper function body that used to be
PgPLSQL and is now SQL.
A spurious "END;" in the end of the body was being interpreted as a
COMMIT;

Thanks.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]