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)
CREATE OR REPLACE FUNCTION InsertCategory(catName varchar, catParent int) RETURNS INTEGER AS $$
CURSOR FOR SELECT currval('categories_categoryid_seq') AS id;
INSERT INTO categories(name, parent) VALUES(catName, catParent);
FETCH id_cursor INTO Id;
$$ LANGUAGE plpgsql;
But my function doesn't work and I don't understand why
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.
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
CREATE or REPLACE FUNCTION InsertCategory(catName varchar, catParent int)
RETURNS integer AS
insertid integer := 0;
SELECT INTO insertid nextval( 'categories_categoryid_seq' );
INSERT INTO categories(name, parent) VALUES(insertid, catName, catParent);
LANGUAGE 'plpgsql' VOLATILE;