From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Return Single Row Result After Inserting (Stored Procedure) |
Date: | 2010-01-08 08:13:02 |
Message-ID: | 20100108081302.GB7677@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2010-01-08 10:28:15 | how much left for restore? |
Previous Message | Yan Cheng Cheok | 2010-01-08 07:50:50 | Return Single Row Result After Inserting (Stored Procedure) |