Re: CREATE FUNCTION Fails with an Insert Statement in it

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