From: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, adrian(dot)klaver(at)gmail(dot)com |
Cc: | tgl(at)sss(dot)pgh(dot)pa(dot)us |
Subject: | Re: Return Single Row Result After Inserting (Stored Procedure) |
Date: | 2010-01-11 03:45:01 |
Message-ID: | 942560.41963.qm@web65707.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)gmail(dot)(dot)com> wrote:
> From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
> Subject: Re: [GENERAL] Return Single Row Result After Inserting (Stored Procedure)
> To: pgsql-general(at)postgresql(dot)org
> Cc: "Yan Cheng Cheok" <yccheok(at)yahoo(dot)com>, tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)klaver(at)gmail(dot)com
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Yan Cheng Cheok | 2010-01-11 06:16:03 | Get Unix timestamp from SQL timestamp through libpq |
Previous Message | Adrian Klaver | 2010-01-11 03:03:16 | Re: Return Single Row Result After Inserting (Stored Procedure) |