0

I am using a function to insert values into a table in postgres. Is it possible to retrieve the inserted/updated/deleted record in same function.

I am new to postgres, so would appreciate if someone could guide or explain how to do this.

Here is my sample function:

CREATE OR REPLACE FUNCTION SampleFunction (in name varchar, in descsciption varchar, in userid varchar) RETURNS void AS
$BODY$
begin     
    insert into sampletable(id,categoryname,categorydesc,createdby) 
    values(default,name,descsciption,userid);        
end
$BODY$
LANGUAGE 'plpgsql'
user2438237
  • 315
  • 2
  • 4
  • 16

1 Answers1

1

Use the RETURNING clause.

 insert into sampletable(id,categoryname,categorydesc,createdby) 
 values(default,name,descsciption,userid)
 returning *

e.g. (Untested):

CREATE OR REPLACE FUNCTION SampleFunction (in name varchar, in descsciption varchar, in userid varchar) RETURNS SETOF sampletable AS
$BODY$
begin     
    RETURN QUERY
    insert into sampletable(id,categoryname,categorydesc,createdby) 
    values(default,name,descsciption,userid)
    returning *;
end
$BODY$
LANGUAGE plpgsql;

Note the use of RETURNS SETOF sampletable to return the rowtype of the table, so you don't have to repeat the column definitions.

For more information, see the PL/PgSQL manual.

Note that in this case you don't need PL/PgSQL at all. If you insist on having this as a function (which isn't necessary) you can just write:

CREATE OR REPLACE FUNCTION SampleFunction (name varchar, descsciption varchar, userid varchar) RETURNS SETOF sampletable AS
$BODY$ 
    insert into sampletable(id,categoryname,categorydesc,createdby) 
    values(default,$1,$2,$3)
    returning *;
$BODY$
LANGUAGE sql;
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • what should be the return type. I guess i need to change the return type as well right? – user2438237 Nov 24 '14 at 01:49
  • getting an error: ERROR: query has no destination for result data – user2438237 Nov 24 '14 at 01:53
  • @user2438237 If you're using it in a function, then like any other set-returning query you must provide a destination. Use `RETURN QUERY INSERT .... RETURNING ...`, or `LOOP` over the results, etc. All the usual stuff you'd do with a `SELECT` works. So yes, if you wish to return results, you will need `RETURNS TABLE` or to use `OUT` and `INOUT` parameters with `RETURNS SETOF RECORD`. – Craig Ringer Nov 24 '14 at 02:00
  • Yes, I am using that in a function. The insert statement is in a function. However, I am not able to understand you. Could you please point me to some document or example where I can see a function with insert statemnt, also returning the inserted record set to UI interface? – user2438237 Nov 24 '14 at 02:04
  • @user2438237 Answer updated with an example. See the user manual for more details on `RETURNING`, `RETURNS TABLE, `RETURNS SETOF`, `OUT` and `INOUT` parameters, etc. – Craig Ringer Nov 24 '14 at 02:32
  • thanks @Craig. the latter one worked.. but the one with sql syntax is throwing an error.. syntax error. But I am OK with using any of that. :) – user2438237 Nov 24 '14 at 02:52
  • @user2438237 My mistake, I forgot to change named columns to $n syntax, fixed. – Craig Ringer Nov 24 '14 at 06:31
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/65539/discussion-between-user2438237-and-craig-ringer). – user2438237 Nov 24 '14 at 18:38