PostgreSQL problem with functions

Hi all.

Maybe this is not such a novice question, but I'm having problem subscribin to some more "professional" PG lists. Here
goes...

I'm trying to make a function in PostgreSQL v7.0. Right now, I'm bugging
with PL/PgSQL and SQL functions.

What I want to achieve is: "insert new row in a table with a possibility of
concurent use". "Concurent use" means that several processes (Apache PHP4)
can call this function simultaneously.

The logical steps, as I see it, are:

1. TRANSACTION start
2. LOCK table
3. GET max(id)+1
4. INSERT new row with primary key from step 2
5. TRANSACTION commit

For this I would like the functionality of PL/PgSQL. I would like it to
return the new_id of the inserted row.

This is what I had in mind.

----

CREATE FUNCTION start_session_pl( int4, VARCHAR(40), VARCHAR(50) ) RETURNS
int4 AS '
DECLARE
  a_id          ALIAS FOR $1;
  a_ss          ALIAS FOR $2;
  a_ip          ALIAS FOR $3;
  curr_time     datetime;
  new_id        int4;
BEGIN
  curr_time := ''now'';
  IF (SELECT id FROM a_user WHERE id=a_id) ISNULL THEN
    RAISE EXCEPTION ''No such ID in admins'';
  END IF;
  BEGIN TRANSACTION;
    LOCK TABLE admin_session IN EXCLUSIVE MODE;
    new_id := (SELECT max(id)+1 FROM admin_session);
    IF new_id ISNULL THEN
      new_id := 1;
    END IF;
    INSERT INTO admin_session VALUES (new_id, a_ss, curr_time, a_id, a_ip);
  COMMIT TRANSACTION;
  RETURN new_id;
END;
' LANGUAGE 'plpgsql';
----

PROBLEM 1
--------------

According to docs, PL/PgSQL has no support for transactions! And, yes it
beltches on any "BEGIN TRANSACTION" or any such.

However, it doesn't complain on "LOCK TABLE". Am I locking it or not? And
what is the lifetime of that lock?

OK, so I though lets write a wrapper function in ordinary SQL, lock table
and call the real function.

----
CREATE FUNCTION start_session( int4, VARCHAR(40), VARCHAR(50) ) RETURNS int4
AS '
  BEGIN TRANSACTION;
  LOCK TABLE admin_session IN EXCLUSIVE MODE;
  SELECT start_session_pl( $1, $2, $3 );
  COMMIT TRANSACTION;
' LANGUAGE 'sql';
----

PROBLEM 2
--------------

I'm having problems creating this SQL function. PSQL complains that the
return type is mismatch. More precisely:

"ERROR:  return type mismatch in function decl: final query is a catalog
utility"

When I put "SELECT 1;" at the end, the function can be created. So, a more
general SELECT is treated as a "catalog utility", while a SELECT with a
determined type is treated as that type. I have tried explicit conversion to
int4, but no go.

What can I do?

Nix.