Re: Return Single Row Result After Inserting (Stored Procedure)

Thanks!

However, we prefer to stick with plpgsql, as rest of our functions are in that language. We need some consistency.

I try to modify my previous stored procedure to.

CREATE OR REPLACE FUNCTION create_lot(text, text, text)
   RETURNS lot AS
$BODY$DECLARE
     configurationFile ALIAS FOR $1;
     operatorName ALIAS FOR $2;
     machineName ALIAS FOR $3;
 BEGIN
     INSERT INTO lot(configuration_file, operator_name, machine_name)
     VALUES(configurationFile, operatorName, machineName) RETURNING *;
 END;$BODY$
   LANGUAGE 'plpgsql' VOLATILE
   COST 100;
 ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres;

However, we get the following error.

SemiconductorInspection=# SELECT * FROM create_lot('a','b','3');
ERROR:  query has no destination for result data
CONTEXT:  PL/pgSQL function "create_lot" line 9 at SQL statement

Any suggestion? Thanks!

Thanks and Regards
Yan Cheng CHEOK