Re: 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