From: | "Jason Tesser" <JTesser(at)nbbc(dot)edu> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | help with a stored procedure |
Date: | 2005-01-03 14:29:18 |
Message-ID: | E618AB1605006B4EA4F29B9F3C6FBCE4E94C@titus.nmi.northlandministries.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Buttafuoco | 2005-01-03 15:14:19 | Re: [ANNOUNCE] PostgreSQL 8.0.0 Release Candidate 3 |
Previous Message | Robert Treat | 2005-01-03 13:35:19 | Re: [ANNOUNCE] PostgreSQL 8.0.0 Release Candidate 3 |