Retrieve the inserted/updated/deleted record in same function in postgres

Question

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'

Answer 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;