Thread: Return Single Row Result After Inserting (Stored Procedure)
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 rowcontains 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 useSETOF. 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"?
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 *; $$languagesql; 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:
> From: Adrian Klaver <adrian.klaver@gmail.com>
> Subject: Re: [GENERAL] Return Single Row Result After Inserting (Stored Procedure)
> To: pgsql-general@postgresql.org
> Cc: "Yan Cheng Cheok" <yccheok@yahoo.com>, tgl@sss.pgh.pa.us
> Date: Monday, January 11, 2010, 11:03 AM
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>