Re: Indicating DEFAULT values in INSERT statement

Le mardi 09 août 2011 à 15:57 -0700, Postgres User a écrit :

>
>
> From a db function, I'd like to force the use of default when an input
> parameter is null.

May be something like this :


CREATE TABLE users (
    id bigint NOT NULL,
    username text NOT NULL,
    is_active boolean DEFAULT false
);


CREATE FUNCTION new_user (text, boolean default 't') RETURNS SETOF users
AS $$

INSERT INTO users(username, is_active) VALUES($1, $2) RETURNING *;

$$ LANGUAGE SQL;

 select new_user('no_status_defined');
         new_user
--------------------------
 (10,no_status_defined,t)
(1 ligne)


As you can see, in the case where the second parameter is absent, the
default set by the function (true) is used.

http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-SQL-PARAMETER-DEFAULTS

It won't work if NOT NULL is set on the field.


--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique