help with a stored procedure

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

Responses

Browse pgsql-general by date

  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