Обсуждение: defaults referencing other columns?

Can a default value reference the value specified for one of the other
columns in the insert?

If you have something like:

CREATE TABLE foo ( id INT PRIMARY KEY,
                   name TEXT NOT NULL,
                   bar TEXT );

can foo.bar have a default that is the result of calling a function on
the value currently being inserted into foo.id or foo.name?

I'd like something along the lines of:

                   bar TEXT DEFAULT somefunc(id)

but how does one specify that the value to be passed to the funtion is
the exact value that insert is inserting into the foo.id column?

And will it work even when the value being inserted into foo.id is
itself the result of a function?  (I presume so, but ....)

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0xED7DAEA6

> Can a default value reference the value specified for one of the other
> columns in the insert?

from:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html


DEFAULT default_expr
The DEFAULT clause assigns a default data value for the column whose column definition it appears
within. The value is any variable-free expression (subqueries and cross-references to other
columns in the current table are not allowed). The data type of the default expression must match
the data type of the column.

The default expression will be used in any insert operation that does not specify a value for the
column. If there is no default for a column, then the default is null.

>                    bar TEXT DEFAULT somefunc(id)

"The value is any variable-free expression".  To me the "id" in somefunc would classify as a
variable.  The other suggestion to use a Rule might work,  however a before insert trigger would
diffently work in this case.

However, why would you what to to store the text anyway? If you wanted to see it you could still
generate "on the fly" with a select statement.

select id, somefunc(id) from foo;

Regards,

Richard Broersma Jr.

>>>>> "RB" == Richard Broersma <rabroersma@yahoo.com> writes:

>> Can a default value reference the value specified for one of the
>> other columns in the insert?

RB> from:
RB> http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

RB> The value is any variable-free expression (subqueries and
RB> cross-references to other columns in the current table are not
RB> allowed).

[SIGH]  I missed that part when reading thru the docs....

I read thru the whole tutorial section of the pdf, and I use \h
liberally in psql, but I didn't notice that point. ;-(

I'll find a different way to do it.

Thanks.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0xED7DAEA6

>>>>> "Jasbinder" == Jasbinder Bali <jsbali@gmail.com> writes:

Jasbinder> There is something called Rule on a table.
Jasbinder> http://www.postgresql.org/docs/8.1/interactive/sql-createrule.html

It looks like I never replied to this. Apologies; I had meant to....

I had looked at rules -- in fact I'd read thru the whole pdf -- but
they didn't seem to fit.

It turned out that a before trigger did, however.  From my first read
thru that section of the docs, I wasn't certain that a before trigger
would Do What I Wanted, but further read-throughs made it clear.

I must've been fatigued when I first read it.

Thanks for the reply.

Oh, and incidently, I took the project in question live tonight, so
all of the help was productive.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 0xED7DAEA6