help with a stored procedure

[Jason Tesser]

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

[Michael Fuhr]

On Mon, Jan 03, 2005 at 08:29:18AM -0600, Jason Tesser wrote:

> 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.

See "Obtaining the Result Status" in the "Basic Statements" section
of the PL/pgSQL documentation.

--
Michael Fuhr
[url]http://www.fuhr.org/~mfuhr/[/url]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings