Thread: Function syntax ?

<div dir="ltr">Does this syntax look correct? Can anyone think of a better way to write this?<br /><br />This function
willaccept two timestamp parameters and determine the highest of the two?<br /><br /><span style="color: rgb(204, 0,
0);">createor replace function fcn_max_dt(p_dt timestamp without time zone,</span><br style="color: rgb(204, 0, 0);"
/><spanstyle="color: rgb(204, 0, 0);">                                      p_dt2 timestamp without time
zone)</span><brstyle="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">returns timestamp without time
zoneas $$  </span><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">  DECLARE</span><br
style="color:rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">      v_dt timestamp without time zone;</span><br
style="color:rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">      v_dt2 timestamp without time zone;</span><br
style="color:rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">   </span><br style="color: rgb(204, 0, 0);"
/><spanstyle="color: rgb(204, 0, 0);">  BEGIN</span><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204,
0,0);">    v_dt := p_dt;</span><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">    v_dt2 :=
p_dt2;</span><brstyle="color: rgb(204, 0, 0);" /><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0,
0);">   if v_dt >= v_dt2 then</span><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">   
returnv_dt;</span><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">    else</span><br
style="color:rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">    return v_dt2;</span><br style="color: rgb(204,
0,0);" /><span style="color: rgb(204, 0, 0);">    end if;</span><br style="color: rgb(204, 0, 0);" /><span
style="color:rgb(204, 0, 0);">        </span><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0,
0);"> END;</span><br style="color: rgb(204, 0, 0);" /><span style="color: rgb(204, 0, 0);">$$ LANGUAGE
'plpgsql';</span><br/><br /><br /></div> 

On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <rubes7202@gmail.com> wrote:
> Does this syntax look correct? Can anyone think of a better way to write
> this?
>
> This function will accept two timestamp parameters and determine the highest
> of the two?
>
> create or replace function fcn_max_dt(p_dt timestamp without time zone,
>                                       p_dt2 timestamp without time zone)
> returns timestamp without time zone as $$
>   DECLARE
>       v_dt timestamp without time zone;
>       v_dt2 timestamp without time zone;
>
>   BEGIN
>     v_dt := p_dt;
>     v_dt2 := p_dt2;
>
>     if v_dt >= v_dt2 then
>     return v_dt;
>     else
>     return v_dt2;
>     end if;
>
>   END;
> $$ LANGUAGE 'plpgsql';

It certainly works, but there's no real need for the declarations.
This works just as well:

create or replace function fcn_max_dt(p_dt timestamp without time zone,                                     p_dt2
timestampwithout time zone)
 
returns timestamp without time zone as $$
   BEGIN   if p_dt >= p_dt2 then   return p_dt;   else   return p_dt2;   end if;
 END;
$$ LANGUAGE 'plpgsql';

try

create or replace function fcn_max_dt(p_dt timestamp without time zone,                                      p_dt2
timestampwithout time zone)
 
returns imestamp without time zone as $$
select greatest($1,$2);
$$ language sql;

or

begin return greatest(p_dt, p_dt2);
end;
$$ language plpgsql;

or
begin if p_dt > p_dt2 then   return p_dt; else   return p_dt2; end if;
end;
$$ language sql;

plpgsql is scripting language and almost is better minimalize number
of statements in function.

Regards
Pavel Stehule

2008/9/9 Ruben Gouveia <rubes7202@gmail.com>:
> Does this syntax look correct? Can anyone think of a better way to write
> this?
>
> This function will accept two timestamp parameters and determine the highest
> of the two?
>
> create or replace function fcn_max_dt(p_dt timestamp without time zone,
>                                       p_dt2 timestamp without time zone)
> returns timestamp without time zone as $$
>   DECLARE
>       v_dt timestamp without time zone;
>       v_dt2 timestamp without time zone;
>
>   BEGIN
>     v_dt := p_dt;
>     v_dt2 := p_dt2;
>
>     if v_dt >= v_dt2 then
>     return v_dt;
>     else
>     return v_dt2;
>     end if;
>
>   END;
> $$ LANGUAGE 'plpgsql';
>
>
>

That's not what I copied and pasted in.  Leave out the  v_dt := p_dt;
>     v_dt2 := p_dt2;
lines and turn the v into p in the rest of the function.


On Tue, Sep 9, 2008 at 12:11 PM, Ruben Gouveia <rubes7202@gmail.com> wrote:
> When i tried that, i got the following error:
>
> create or replace function fcn_max_dt(p_dt timestamp without time zone,
>                                       p_dt2 timestamp without time zone)
> returns timestamp without time zone as $$
>
>   BEGIN
>     v_dt := p_dt;
>     v_dt2 := p_dt2;
>
>     if v_dt >= v_dt2 then
>     return v_dt;
>     else
>     return v_dt2;
>     end if;
>
>   END;
> $$ LANGUAGE 'plpgsql';
>
> ERROR:  syntax error at or near "v_dt" at character 1
> QUERY:  v_dt :=  $1
> CONTEXT:  SQL statement in PL/PgSQL function "fcn_max_dt" near line 3
>
> ********** Error **********
>
> ERROR: syntax error at or near "v_dt"
> SQL state: 42601
> Context: SQL statement in PL/PgSQL function "fcn_max_dt" near line 3
>
>
>
>
>
>
>
>
> On Tue, Sep 9, 2008 at 11:07 AM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <rubes7202@gmail.com>
>> wrote:
>> > Does this syntax look correct? Can anyone think of a better way to write
>> > this?
>> >
>> > This function will accept two timestamp parameters and determine the
>> > highest
>> > of the two?
>> >
>> > create or replace function fcn_max_dt(p_dt timestamp without time zone,
>> >                                       p_dt2 timestamp without time zone)
>> > returns timestamp without time zone as $$
>> >   DECLARE
>> >       v_dt timestamp without time zone;
>> >       v_dt2 timestamp without time zone;
>> >
>> >   BEGIN
>> >     v_dt := p_dt;
>> >     v_dt2 := p_dt2;
>> >
>> >     if v_dt >= v_dt2 then
>> >     return v_dt;
>> >     else
>> >     return v_dt2;
>> >     end if;
>> >
>> >   END;
>> > $$ LANGUAGE 'plpgsql';
>>
>> It certainly works, but there's no real need for the declarations.
>> This works just as well:
>>
>> create or replace function fcn_max_dt(p_dt timestamp without time zone,
>>                                      p_dt2 timestamp without time zone)
>> returns timestamp without time zone as $$
>>
>>    BEGIN
>>    if p_dt >= p_dt2 then
>>    return p_dt;
>>    else
>>    return p_dt2;
>>    end if;
>>
>>  END;
>> $$ LANGUAGE 'plpgsql';
>
>

Scott Marlowe wrote:
> On Tue, Sep 9, 2008 at 11:55 AM, Ruben Gouveia <rubes7202@gmail.com> wrote:
>> Does this syntax look correct? Can anyone think of a better way to write
>> this?
>>
>> This function will accept two timestamp parameters and determine the highest
>> of the two?
[snip]
> It certainly works, but there's no real need for the declarations.
> This works just as well:

I'll see your improved function, and raise you some built-ins.

=> \df time*larger                                                   List of functions  Schema   |        Name        |
    Result data type       |          Argument data types
 

------------+--------------------+-----------------------------+----------------------------------------------------------pg_catalog
|time_larger        | time without time zone      | time
 
without time zone, time without time zonepg_catalog | timestamp_larger   | timestamp without time zone |
timestamp without time zone, timestamp without time zonepg_catalog | timestamptz_larger | timestamp with time zone
|
timestamp with time zone, timestamp with time zonepg_catalog | timetz_larger      | time with time zone         | time
with time zone, time with time zone
(4 rows)

Also available for other built-in types. Been there ages, used by
aggregate funcs iirc.

--  Richard Huxton Archonet Ltd