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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
