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