Custom functions for default values for columns on insert

From: "Lim Berger" <straightfwd007(at)gmail(dot)com>
To: "Postgresql General List" <pgsql-general(at)postgresql(dot)org>
Subject: Custom functions for default values for columns on insert
Date: 2007-08-15 04:07:39
Message-ID: 69d2538f0708142107x489c36f4o4b2e92dbb14178c3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The "default" values of a column during table definition do not accept
values generated by passing another column's value through my own
function. So I try to do this with a rule as follows. The name of my
function in this example is MYFUNCTION.

drop table test cascade;

create table test (id serial primary key, nowd timestamp without time
zone, processed_id varchar(10));

create or replace rule test_ins as on insert to test
DO UPDATE test
SET processed_id = MYFUNCTION(NEW.id)
WHERE id = NEW.id
;

insert into test (nowd) values (current_timestamp);
insert into test (nowd) values (now());
select * from test;

This results in the "processed_id" column coming up blank. What am I
doing wrong? How can I make sure that upon insert of a row, the value
of one column ("id in my example) is used to immediately generate the
value of another column ("process_id" in my example).

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Trevor Talbot 2007-08-15 04:10:20 Re: language interface in postgresql
Previous Message Harpreet Dhaliwal 2007-08-15 04:02:54 Transactional DDL