Thread: calculated default

I have a table with a sequence as its primary key.
On insert I would like to have all its fields (except the primary key)
default to the last one in the table (order by id desc limit 1).
I guess I should use a trigger to do this but I don't know how.

Thanks for the help.
SWK

SunWuKung wrote:
> I have a table with a sequence as its primary key.
> On insert I would like to have all its fields (except the primary key)
> default to the last one in the table (order by id desc limit 1).
> I guess I should use a trigger to do this but I don't know how.

I'm not sure you can really change the DEFAULT clause with a trigger,
but you can get close.

You'll want a BEFORE INSERT trigger (in plpgsql).
Select into a variable of type RECORD your last row (... ORDER BY id
DESC LIMIT 1)
Update the values of the NEW variable where they are currently NULL.
Return NEW

Two points to consider:
1. Consider locking the table to prevent inserts while doing this.
Whether you need to do so depends on what you want to happen when two
inserts happen at the same time.
2. What happens if there is no previous row in the table?


--
   Richard Huxton
   Archonet Ltd

> I have a table with a sequence as its primary key.
> On insert I would like to have all its fields (except the primary key)
> default to the last one in the table (order by id desc limit 1).
> I guess I should use a trigger to do this but I don't know how.

How about this:
INSERT INTO table
SELECT nextval('seq'), col1, col2...
FROM table
WHERE pkey = curval('seq');


Regards,

Hitoshi Harada

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of SunWuKung
> Sent: Thursday, November 16, 2006 5:48 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] calculated default
>
> I have a table with a sequence as its primary key.
> On insert I would like to have all its fields (except the primary key)
> default to the last one in the table (order by id desc limit 1).
> I guess I should use a trigger to do this but I don't know how.
>
> Thanks for the help.
> SWK
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match