Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: Stored Function Problems

    Hi all

    Sorry i'm a newbie to stored functions, and I'm trying to write a function to insert a row and return its new row id. From reading the manual I've got this far (see below)

    PHP Code:
    CREATE OR REPLACE FUNCTION  InsertCategory(catName varcharcatParent intRETURNS INTEGER AS $$
        DECLARE
            
    Id_cursor 
                CURSOR 
    FOR SELECT currval('categories_categoryid_seq') AS id;
            
    Id 
                Ids
    %ROWTYPE;
        
    BEGIN
            INSERT INTO categories
    (nameparentVALUES(catNamecatParent);
            
    OPEN Id_cursor;
            
    FETCH id_cursor INTO Id;
        RETURN 
    Id.id;
        
    END;
    $$ 
    LANGUAGE plpgsql
    But my function doesn't work and I don't understand why

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    I'm wondering if this is the most efficient way to accomplish what you want. With a trigger function (after insert) you could simply have new.id returned.

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    Ok so how do I do it baring in mind that, I don't understand the pl/psql language at all despite reading the manual and trying to understand it
    Last edited by 949Stu; 10-05-05 at 06:20.

  4. #4
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    I'm not sure what you want to do with the id value. Pgsql is a very simple scripting language - from your code it looks like the syntax is very similar to PHP. I noticed that you had some trouble with the pgsql chapter in the PostgreSQL manual, but the syntax is pretty similar to the PHP code you wrote, so I think the learning curve won't be too great. There is also an example of a trigger function. BTW, I think you can write the function in PHP - I believe there is a module for it. Perhaps the conceptual glitch is that with PostgreSQL, lots of things are done internally within the database rather than in the front end.

    Basically do the following (using pgAdmin III). Right click Trigger Functions and select the Create a New Function option. This will give you the editing window to insert your code. The function will have an optional Declare portion (where variables are declared) and a body starting with Begin and ending with End;. Again, the code is pretty straightforward, and you will no doubt want to do something with new.id (the id value of the newly inserted record). After the function is written, expand the table heading and right-click Triggers, selecting the Create Trigger option. Here you link the trigger function to the table. Select "After" (since you want the action to occur after the new record is saved) and "On Insert" (perhaps also "On Update"). Select the function name. Done.

    The only potential complexity is that Trigger functions (unlike other stored functions) have some restrictions on how variables are passed (they use a special array rather than the usual direct passing). This is explained in the manual and isn't really too bad.
    Last edited by ByteRyder52; 10-05-05 at 14:04.

  5. #5
    Join Date
    Sep 2005
    Location
    Sweden
    Posts
    10
    I do the following when I want to return the new row id. I am also
    pretty much new to postgre so I am not sure if this is the most efficient
    way to accomplish want you want.
    I wrote it from scratch so one or another error could have found its
    way in there


    Code:
    CREATE or REPLACE FUNCTION InsertCategory(catName varchar, catParent int)
    RETURNS integer AS 
    $BODY$
    DECLARE
           insertid integer := 0;
    BEGIN
    
    SELECT INTO insertid nextval( 'categories_categoryid_seq' );
    INSERT INTO categories(name, parent) VALUES(insertid, catName, catParent);
    RETURN insertid;
    
    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE;
    Good luck!

Posting Permissions

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