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 serial 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