Hi,

I'm trying to write an INSERT INTO statement that will use a DEFAULT
value when an input parameter is null.

Here's the function that fails to compile. I tried replacing Coalesce
with a Case statement but that fails as well. Note that if you
replace the condition with a simple 'Default' it compiles
successfully. Any ideas?


CREATE OR REPLACE FUNCTION "name_add" (
p_name varchar,
p_created_date date
) RETURNS integer AS
$body$
DECLARE
BEGIN
INSERT INTO names
(
name,
created_date
)
VALUES
(
p_name,
Coalesce(p_created_date, DEFAULT)
);
Return 1;
END ;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Search Discussions

  • Sim Zacks at Sep 29, 2009 at 6:44 am

    I'm trying to write an INSERT INTO statement that will use a DEFAULT
    value when an input parameter is null.
    Neither of my 2 methods are pretty.
    1) Use a trigger.
    2) Grab and cast the default value from the information_schema.columns
    view and plug it in.

    Another option is to build your insert sql as a string and then execute it.

    Sim
  • Postgres User at Sep 29, 2009 at 6:53 am

    I'm trying to write an INSERT INTO statement that will use a DEFAULT
    value when an input parameter is null.
    Neither of my 2 methods are pretty.
    1) Use a trigger.
    2) Grab and cast the default value from the information_schema.columns
    view and plug it in.

    Another option is to build your insert sql as a string and then execute it.

    Sim
    Thanks... those approaches came to mind. But I need something more
    flexible as the Insert functions are generated by an ORM layer. It
    has to be simpler.
  • Richard Huxton at Sep 29, 2009 at 8:34 am

    Postgres User wrote:
    I'm trying to write an INSERT INTO statement that will use a DEFAULT
    value when an input parameter is null.
    Neither of my 2 methods are pretty.
    1) Use a trigger.
    2) Grab and cast the default value from the information_schema.columns
    view and plug it in.

    Another option is to build your insert sql as a string and then execute it.
    Thanks... those approaches came to mind. But I need something more
    flexible as the Insert functions are generated by an ORM layer. It
    has to be simpler.
    You're not going to get anything simpler than building a string and
    calling execute. Nor more flexible, come to think of it.

    BTW - surely the ORM should be generating your defaults for you?

    --
    Richard Huxton
    Archonet Ltd
  • Andreas Kretschmer at Sep 29, 2009 at 6:54 am

    In response to Sim Zacks :

    I'm trying to write an INSERT INTO statement that will use a DEFAULT
    value when an input parameter is null.
    Neither of my 2 methods are pretty.
    1) Use a trigger.
    2) Grab and cast the default value from the information_schema.columns
    view and plug it in.

    Another option is to build your insert sql as a string and then execute it.
    Another option:

    rewrite your function:

    CREATE OR REPLACE FUNCTION "name_add" (p_name varchar, p_created_date
    date) RETURNS integer AS $$ BEGIN case when $2 is null then INSERT INTO
    names(name, created_date) values (p_name, default); else insert into
    names values ($1, $2); end case; return 1; end; $$ language plpgsql;


    Andreas
    --
    Andreas Kretschmer
    Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
  • Postgres User at Sep 29, 2009 at 11:04 am

    I'm trying to write an INSERT INTO statement that will use a DEFAULT
    value when an input parameter is null.
    Neither of my 2 methods are pretty.
    1) Use a trigger.
    2) Grab and cast the default value from the information_schema.columns
    view and plug it in.

    Another option is to build your insert sql as a string and then execute it.
    Another option:

    rewrite your function:

    CREATE OR REPLACE FUNCTION "name_add" (p_name varchar, p_created_date
    date) RETURNS integer AS $$ BEGIN case when $2 is null then INSERT INTO
    names(name, created_date) values (p_name,  default); else insert into
    names values ($1, $2); end case; return 1; end; $$ language plpgsql;

    Andreas Kretschmer
    Again, this approach works for a simple example. But for a larger
    function with any number of input params and multiple columns with
    default values, it's not practical for an ORM code generator.
  • Sam Mason at Sep 29, 2009 at 11:45 am

    On Tue, Sep 29, 2009 at 04:04:46AM -0700, Postgres User wrote:
    I'm trying to write an INSERT INTO statement that will use a DEFAULT
    value when an input parameter is null.
    [ workaround given ]
    Again, this approach works for a simple example. But for a larger
    function with any number of input params and multiple columns with
    default values, it's not practical for an ORM code generator.
    Just to reiterate what Richard said; this is really the duty of the ORM.

    If you're telling the database to insert a NULL then that's what the
    database should do. If you tell it to insert a DEFAULT that that's what
    it should do. The fact that your ORM doesn't distinguish between the
    two is a bug in your ORM and should really be fixed there.

    One of PG's design mantras is to do (exactly) what the user says or tell
    them why it's not possible. Silently changing NULLs into DEFAULT values
    doesn't seem like correct behavior and will generally come back and bite
    you later on---for example, what if the user really does want to insert
    a NULL value into *any* column that has a non-NULL DEFAULT value.
  • Brent_despain at Sep 29, 2009 at 2:15 pm
    Don't know if this will get attached to the conversation, but here is how
    I have dealt with this.

    insert into names (name, created_date) values ($1, default);
    update names n set created_date = coalesce($2, n.craeted_date) where name
    = $1;

    So basically insert all of your non-defaulted columns with the primary
    key. This will put the defaults into the table. Then update with
    coalesce the values passed into the proc. The coalesce will use the
    parameter unless it is NULL. If it is NULL it will use the default from
    the table. This is inefficient since it will version the row in the table
    for each call to the proc.

    Brent DeSpain

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedSep 29, '09 at 6:25a
activeSep 29, '09 at 2:15p
posts8
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase