Return Single Row Result After Inserting (Stored Procedure)

In response to Yan Cheng Cheok :

Hello all,

I have the following procedure. I wish it will return a single row
result to caller, after I insert the value (as the row contains
several auto generated fields), without perform additional SELECT
query.

According to
http://wischner.blogspot.com/2009/03/creating-stored-procedure-function.html,
my guess is that, I need to use SETOF. However, pgAdmin doesn't allow
me to enter "SETOF" in "Return Type".

However, it let me enter "lot" (lot is the name of the table)

May I know how can I modified the following function, to let it returns my newly inserted row?

CREATE OR REPLACE FUNCTION create_lot(text, text, text, 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);
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres;

You have defined a function with 6 input-parameters, but inside the
function there are only 3 used. Why?

You can rewrite your function, simple example:

-- create a simple table with 2 columns
test=# create table foo (col1 int, col2 text);
CREATE TABLE

-- create a simple function
test=*# create or replace function insert_foo(int, text) returns foo as $$insert into foo values ($1, $2) returning *; $$language sql;
CREATE FUNCTION

-- use that function
test=*# select * from insert_foo(1, 'test') ;
col1 | col2
------+------
1 | test
(1 row)

-- check, if our table contains the new record
test=*# select * from foo;
col1 | col2
------+------
1 | test
(1 row)

Yeah!

For such simple task you can use language SQL instead ig pl/pgsql.

Thanks and Regards
Yan Cheng CHEOK

p/s May I know what is the purpose of "COST 100"?

It is a hint for the planner to calculate the costs for the function.
You can omit this parameter.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

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

On Sunday 10 January 2010 5:49:38 pm Yan Cheng Cheok wrote:

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

See here;
http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

--
Adrian Klaver
adrian.klaver@gmail.com

Thanks a lot. I solved my problem by using this.

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;
_lot lot;
BEGIN
INSERT INTO lot(configuration_file, operator_name, machine_name)
VALUES(configurationFile, operatorName, machineName) RETURNING * INTO _lot;
return _lot;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres;

Thanks and Regards
Yan Cheng CHEOK

--- On Mon, 1/11/10, Adrian Klaver <adrian.klaver@gmail..com> wrote: