Hi,

According to the docs, the DEFAULT keyword lets you explicitly insert a
field's default value in an INSERT statement.

From a db function, I'd like to force the use of default when an input
parameter is null. This syntax looks correct but fails to compile.

Any suggestions?

INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status,
Case When p_created_ts Is Not Null Then p_created_ts Else DEFAULT
End
);

I tried replacing the Case statement as follows, but it fails also:
Coalesce(p_created_ts, DEFAULT)

Thanks

Search Discussions

  • Diego Augusto Molina at Aug 9, 2011 at 11:47 pm
    Hi, when posting SQL it may be a good practice to post your PG
    version so that answers may be more accurate and better fit your
    needs. In this case, I don't think you'll be able to do what you are
    trying to, because as of my understanding the "DEFAULT" is not part of
    an expression but a keyword itself which is to be written *instead* of
    an expression. I get your point and seems pretty fair to me, but if
    I'm right you don't have access to the default value of the column
    from within an expression.
    Assuming you're using the latest stable version of PostgreSQL (9.0
    as of today), you can check the following page for an online reading
    reference of the INSERT statement:
    http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
    If you look carefully, you'll see that for the value of a column you
    may write: the "DEFAULT" keyword [exclusive or] an expression (which
    includes specific values).
    The [exclusive or] is deduced because of the pipe (`|') between the
    two choices which are embraced by curly braces.
    So, if you were able to use the "DEFAULT" keyword inside an
    expression, instead of having the "{ expression | DEFAULT }" syntax
    you would have the "expression" syntax, which would include the case
    of having just "DEFAULT" as an expression. It's just a matter of
    understanding the syntax.

    Cheers!

    --
    Diego Augusto Molina
    diegoaugustomolina@gmail.com

    ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
    desestimados.
    EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
    LINK: http://www.gnu.org/philosophy/no-word-attachments.html
  • Postgres User at Aug 9, 2011 at 11:56 pm
    Thanks for the previous posts. I am in fact running 9.0 'nix and am unable
    to find a way to embed DEFAULT within an expression.
    I can always rewrite the function to call EXECUTE but that's not a very good
    solution in this case because many of our db functions are code generated.
    On Tue, Aug 9, 2011 at 4:47 PM, Diego Augusto Molina wrote:

    Hi, when posting SQL it may be a good practice to post your PG
    version so that answers may be more accurate and better fit your
    needs. In this case, I don't think you'll be able to do what you are
    trying to, because as of my understanding the "DEFAULT" is not part of
    an expression but a keyword itself which is to be written *instead* of
    an expression. I get your point and seems pretty fair to me, but if
    I'm right you don't have access to the default value of the column
    from within an expression.
    Assuming you're using the latest stable version of PostgreSQL (9.0
    as of today), you can check the following page for an online reading
    reference of the INSERT statement:
    http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
    If you look carefully, you'll see that for the value of a column you
    may write: the "DEFAULT" keyword [exclusive or] an expression (which
    includes specific values).
    The [exclusive or] is deduced because of the pipe (`|') between the
    two choices which are embraced by curly braces.
    So, if you were able to use the "DEFAULT" keyword inside an
    expression, instead of having the "{ expression | DEFAULT }" syntax
    you would have the "expression" syntax, which would include the case
    of having just "DEFAULT" as an expression. It's just a matter of
    understanding the syntax.

    Cheers!

    --
    Diego Augusto Molina
    diegoaugustomolina@gmail.com

    ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
    desestimados.
    EN: Please, avoid attaching Microsoft Office documents. They shall be
    discarded.
    LINK: http://www.gnu.org/philosophy/no-word-attachments.html
  • Diego Augusto Molina at Aug 10, 2011 at 12:35 am
    This is a bit hacky, but it may work (I haven't tried it yet). If it
    works let us know and if it doesn't then maybe we can debug it and get
    something useful, or move on to another solution.

    <code>
    INSERT INTO public.test
    (
    userid, object_id, user_notes, object_status, created_ts
    )
    VALUES
    (
    p_userid, p_obj_id, p_user_notes, p_obj_status,
    Case When p_created_ts Is Not Null Then p_created_ts Else
    ( -- You may want to be sure the field has a default value.
    SELECT d.adsrc -- or should it be d.adbin?
    FROM
    pg_catalog.pg_attribute a INNER JOIN
    pg_catalog.pg_attrdef d ON (a.attnum = d.adnum)
    WHERE
    a.attname = 'created_ts' AND
    a.attrelid = 'public.test'::REGCLASS AND
    d.adrelid = 'public.test'::REGCLASS
    ) End
    );
    </code>

    Well (thinking it thoroughly) it won't work at all as is. It will just
    put the source code of the default expression but you would need to
    *interpret* it first.
    Looked for a way to do this (without `EXECUTE') but couldn't find it. Sorry.

    --
    Diego Augusto Molina
    diegoaugustomolina@gmail.com

    ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
    desestimados.
    EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
    LINK: http://www.gnu.org/philosophy/no-word-attachments.html
  • Postgres User at Aug 10, 2011 at 1:10 am
    Good idea but you're right, if the default value is an expression such as a
    the next serial value, it will need to be executed first. If Execute
    doesn't return the interpreted value, I don't know of a way to make it
    work..
    On Tue, Aug 9, 2011 at 5:35 PM, Diego Augusto Molina wrote:

    This is a bit hacky, but it may work (I haven't tried it yet). If it
    works let us know and if it doesn't then maybe we can debug it and get
    something useful, or move on to another solution.

    <code>
    INSERT INTO public.test
    (
    userid, object_id, user_notes, object_status, created_ts
    )
    VALUES
    (
    p_userid, p_obj_id, p_user_notes, p_obj_status,
    Case When p_created_ts Is Not Null Then p_created_ts Else
    ( -- You may want to be sure the field has a default value.
    SELECT d.adsrc -- or should it be d.adbin?
    FROM
    pg_catalog.pg_attribute a INNER JOIN
    pg_catalog.pg_attrdef d ON (a.attnum =
    d.adnum)
    WHERE
    a.attname = 'created_ts' AND
    a.attrelid = 'public.test'::REGCLASS AND
    d.adrelid = 'public.test'::REGCLASS
    ) End
    );
    </code>

    Well (thinking it thoroughly) it won't work at all as is. It will just
    put the source code of the default expression but you would need to
    *interpret* it first.
    Looked for a way to do this (without `EXECUTE') but couldn't find it.
    Sorry.

    --
    Diego Augusto Molina
    diegoaugustomolina@gmail.com

    ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
    desestimados.
    EN: Please, avoid attaching Microsoft Office documents. They shall be
    discarded.
    LINK: http://www.gnu.org/philosophy/no-word-attachments.html
  • Vincent Veyron at Aug 12, 2011 at 8:31 pm

    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
  • Vincent Veyron at Aug 12, 2011 at 8:31 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 9, '11 at 10:57p
activeAug 12, '11 at 8:31p
posts7
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase