Professional Web Applications Themes

help with a stored procedure - PostgreSQL / PGSQL

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

  1. #1

    Default help with a stored procedure

    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


    Jason Tesser Guest

  2. #2

    Default Re: help with a stored procedure

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

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

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

    Michael Fuhr Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  3. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 PM
  4. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  5. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139