Re: [GENERAL] Return Single Row Result After Inserting (Stored Procedure)
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 wrote:
> From: Adrian Klaver
> Subject: Re: [GENERAL] Return Single Row Result After Inserting (Stored
> Procedure)
> To: pgsql-general@postgresql.org
> Cc: "Yan Cheng Cheok" , t...@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.kla...@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
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Return Single Row Result After Inserting (Stored Procedure)
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.kla...@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
Re: [GENERAL] Return Single Row Result After Inserting (Stored Procedure)
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 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"? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
