Обсуждение: Using Insert - Default in a condition expression ??

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;

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

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

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

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

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.

--
  Sam  http://samason.me.uk/