Skip site navigation (1) Skip section navigation (2)

PostgreSQL problem with functions

From: "Nikola Milutinovic" <Nikola(dot)Milutinovic(at)ev(dot)co(dot)yu>
To: "PostgreSQL general" <pgsql-general(at)postgresql(dot)org>, "PostgreSQL novice" <pgsql-novice(at)postgresql(dot)org>
Subject: PostgreSQL problem with functions
Date: 2001-06-12 09:01:49
Message-ID: 007301c0f31e$50a0d560$6e3da8c0@ev.co.yu (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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.

Responses

pgsql-novice by date

Next:From: Mohammed ADate: 2001-06-12 09:37:43
Subject: Urgent
Previous:From: Giorgio A.Date: 2001-06-12 07:53:38
Subject: Re: case-insensitive SELECT

pgsql-general by date

Next:From: CLECH Armelle FTRD/DMI/LANDate: 2001-06-12 10:13:17
Subject: PQFInish doesn't work
Previous:From: Tom LaneDate: 2001-06-12 07:24:05
Subject: Re: stumped on view/rule/delete problem.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group