I am new to postgres stored procedures and would like a little help. My function basically takes 2 arguments and inserts data into a table from a select statement. I want it to return the number of records inserted. I am not sure what the best way to do this is. Here is my function
CREATE OR REPLACE FUNCTION "public"."workstudyrollover" (INTEGER, INTEGER) RETURNS INTEGER AS
$$
declare
currentSemester alias for $1;
oldSemester alias for $2;
begin
insert into tblworkstudy (transcriptlink, deptlink, payrate, current)
Select distinct transcriptid, ws.deptlink, ws.payrate, ws.current from (SELECT DISTINCT
public.tblworkstudy.transcriptlink,
public.tblworkstudy.deptlink,
public.tblindividual.indid,
public.tblworkstudy.payrate,
public.tblworkstudy.current
FROM public.tblworkstudy
INNER JOIN public.tbltranscript ON (public.tblworkstudy.transcriptlink = public.tbltranscript.transcriptid)
INNER JOIN public.tblindividual ON (public.tbltranscript.indlink = public.tblindividual.indid)
WHERE public.tbltranscript.semesterlink = oldSemester and tblworkstudy.deptlink is not null) as ws
inner Join (Select DISTINCT tbltranscript.transcriptid, tbltranscript.indlink from tbltranscript where tbltranscript.semesterlink = currentSemester) as nws ON (ws.indid = nws.indlink);
return 1;
end;
$$
LANGUAGE 'plpgsql' VOLATILE;
And I call it with
select * from workstudyrollover(94, 92);
Thank you for any help given
