From: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Return Single Row Result After Inserting (Stored Procedure) |
Date: | 2010-01-08 07:50:50 |
Message-ID: | 753738.66184.qm@web65711.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
Thanks and Regards
Yan Cheng CHEOK
p/s May I know what is the purpose of "COST 100"?
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2010-01-08 08:13:02 | Re: Return Single Row Result After Inserting (Stored Procedure) |
Previous Message | Yan Cheng Cheok | 2010-01-08 06:20:49 | check the execution status of stored procedure |