default value based on select

Hello

On 05/04/2008, Pedro Doria Meunier <pdoria@netmadeira.com> wrote:

Hi,

Is it possible to have a table's column default value set to some form of
select?

directly no, but you can use function

postgres=# create table a(a integer);
CREATE TABLE

postgres=# create function mx() returns int as $$select max(a) from
a$$ language sql volatile;
CREATE FUNCTION
postgres=# create table b(x integer default mx());
CREATE TABLE

Regards
Pavel Stehule

On Sat, 5 Apr 2008, Pedro Doria Meunier <pdoria@netmadeira.com> writes:

Is it possible to have a table's column default value set to some form of
select?

AFAIK, you cannot provide sub-selects in the default values of a
field. E.g.

CREATE TABLE foo (bar int DEFAULTS (SELECT ...), ...);

For this, I know two solutions:

1. You can create an SQL function that issues the related sub-select and
call this function as the default value of the column.

2. Create a BEFORE INSERT/UPDATE trigger that issues the related
sub-select if related column appears to be un-specified.

Regards.

Pavel and Volkan

Thank you for your kind (and prompt) replies.

Let me try to better explain my needs:
The record being created has a column with an Id that has to be passed as an
argument to the function that, in turn, fills the other column in the same
record.

Does this even make sense? :)

Kind regards,
--
Pedro Doria Meunier
Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y
9125-162 Caniço
Madeira - Portugal
--------------------------------------------------
Skype : pdoriam
Mobile: +351961720188

On Sat, 5 Apr 2008, Pedro Doria Meunier <pdoria@netmadeira.com> writes:

The record being created has a column with an Id that has to be passed as an
argument to the function that, in turn, fills the other column in the same
record.

Doesn't BEFORE INSERT/UPDATE trigger solve this problem? (If not, I
wonder what is missing.)

Regards.