Postgres Pro
Downloads
Home   >   mailing lists

Thread: default value based on select

default value based on select

From
Pedro Doria Meunier
Date:
Hi,

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

e.g. picking another table's value for a given SELECT ... WHERE ...

Already thankful for any insight,
--
Pedro Doria Meunier
Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y
9125-162 Caniço
Madeira - Portugal
--------------------------------------------------
Skype : pdoriam
Mobile: +351961720188
Attachment

Re: default value based on select

From
"Pavel Stehule"
Date:
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
>  e.g. picking another table's value for a given SELECT ... WHERE ...
>
>  Already thankful for any insight,
>
> --
>  Pedro Doria Meunier
>  Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y
>  9125-162 Caniço
>  Madeira - Portugal
>  --------------------------------------------------
>  Skype : pdoriam
>  Mobile: +351961720188
>
>

Re: default value based on select

From
Volkan YAZICI
Date:
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.

Re: default value based on select

From
Pedro Doria Meunier
Date:
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
Attachment

Re: default value based on select

From
Volkan YAZICI
Date:
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.