Обсуждение: return value of the trigger function

Hi

I have big problem with a trigger function.
Defs:

CREATE TABLE foo (id int2, name varchar(20));

foo.id cannot be serial or autoint because it will not be unique.

Now I need a trigger that return foo.id of the inserted record.

INSERT INTO foo VALUES (max(foo.id)+1,'junk');


IMHO the trigger should be:

CREATE TRIGGER tr_get_new_id
AFTER INSERT on foo
ON EACH ROW
EXECUTE PROCEDURE get_new_id();

But I cannot create the function :(
I don't know what type should be input and how about output? trigger or 
int2?

TIA,


-- 
Jarek Pudelko
JP272-RIPE

On Mon, Jan 10, 2005 at 15:15:33 +0100, Jarek Pude?ko <jarek.pudelko@tech.ceti.pl> wrote:
> Hi
> 
> I have big problem with a trigger function.
> Defs:
> 
> CREATE TABLE foo (id int2, name varchar(20));
> 
> foo.id cannot be serial or autoint because it will not be unique.
> 
> Now I need a trigger that return foo.id of the inserted record.
> 
> INSERT INTO foo VALUES (max(foo.id)+1,'junk');

If you are really doing the above, you probably do want to use serial.

If you insist on not doing this your app should do something like
lock table, select max(foo.id)+1 from id, and then nsert using the
value returned bye the select and then whatever else you want to do.

> 
> 
> IMHO the trigger should be:
> 
> CREATE TRIGGER tr_get_new_id
> AFTER INSERT on foo
> ON EACH ROW
> EXECUTE PROCEDURE get_new_id();
> 
> But I cannot create the function :(
> I don't know what type should be input and how about output? trigger or 
> int2?
> 
> TIA,
> 
> 
> -- 
> Jarek Pudelko
> JP272-RIPE
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)