Re: Return Single Row Result After Inserting (Stored Procedure)

[Yan Cheng Cheok <yccheok@xxxxxxxxx>]

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@xxxxxxxxxx> wrote:

> From: Adrian Klaver <adrian.klaver@xxxxxxxxx>
> Subject: Re:  Return Single Row Result After Inserting (Stored Procedure)
> To: pgsql-general@xxxxxxxxxxxxxx
> Cc: "Yan Cheng Cheok" <yccheok@xxxxxxxxx>, tgl@xxxxxxxxxxxxx
> 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@xxxxxxxxx
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 





-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general