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.
