Обсуждение: CREATE FUNCTION Fails with an Insert Statement in it

I have created the following function :


CREATE OR REPLACE FUNCTION  sp_insert_tbl_l_header(int4,text)
  RETURNS bool AS
$BODY$
INSERT INTO tbl_xyz
(unmask_id,email_from)
VALUES ($1,$2)
$BODY$
  LANGUAGE 'sql' VOLATILE;

when i try to create this fucntion by running this script, i get the
following error:

ERROR:  return type mismatch in function declared to return boolean
DETAIL:  Function's final statement must be a SELECT.
CONTEXT:  SQL function "sp_insert_tbl_xyz"

So i think there is some problem with the return type of the function
when it has an insert statement but really don't know what should be
the return type of this function.

Thanks,
Jas

On 1/23/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:
> I have created the following function :
>
>
> CREATE OR REPLACE FUNCTION  sp_insert_tbl_l_header(int4,text)
>   RETURNS bool AS
> $BODY$
> INSERT INTO tbl_xyz
> (unmask_id,email_from)
> VALUES ($1,$2)
> $BODY$
>   LANGUAGE 'sql' VOLATILE;
>
> when i try to create this fucntion by running this script, i get the
> following error:
>
> ERROR:  return type mismatch in function declared to return boolean
> DETAIL:  Function's final statement must be a SELECT.
> CONTEXT:  SQL function "sp_insert_tbl_xyz"
>
> So i think there is some problem with the return type of the function
> when it has an insert statement but really don't know what should be
> the return type of this function.

declare the function to return void.

merlin

am  Tue, dem 23.01.2007, um 11:51:00 -0500 mailte Jasbinder Singh Bali folgendes:
> I have created the following function :
>
>
> CREATE OR REPLACE FUNCTION  sp_insert_tbl_l_header(int4,text)
>  RETURNS bool AS
> $BODY$
> INSERT INTO tbl_xyz
> (unmask_id,email_from)
> VALUES ($1,$2)
> $BODY$
>  LANGUAGE 'sql' VOLATILE;
>
> when i try to create this fucntion by running this script, i get the
> following error:
>
> ERROR:  return type mismatch in function declared to return boolean
> DETAIL:  Function's final statement must be a SELECT.
> CONTEXT:  SQL function "sp_insert_tbl_xyz"

I can see following mistakes:

- no return-statement
- missing ';' after the line contains VALUES ($1,$2)
- no begin
- no end;



Perhaps you want to do something like this:

CREATE OR REPLACE FUNCTION sp_insert_tbl_l_header(int4,text) RETURNS bool AS $BODY$
begin
    INSERT INTO foo (unmask_id,email_from) VALUES ($1,$2);
    return 'true'::bool;
end;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

(change table-name foo to tbl_xyz ... )


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net