two date functions

More times I needed a function that returns specific date of current years or specific date of current month.

I wrote two functions that solve this request:

CREATE OR REPLACE FUNCTION public.this_month_day(integer)
 RETURNS date
 LANGUAGE sql
AS $function$
select (date_trunc('month', current_date) + ($1 - 1) * interval '1 day')::date
$function$;

CREATE OR REPLACE FUNCTION public.this_year_day(integer, integer)
 RETURNS date
 LANGUAGE sql
AS $function$
select (date_trunc('year', current_date) + ($1 - 1) * interval '1 month' + ($2-1) * interval '1day')::date
$function$;

postgres=# select this_year_day(7,15);
 this_year_day 
---------------
 2013-07-15
(1 row)

postgres=# select this_month_day(15);
 this_month_day 
----------------
 2013-07-15
(1 row)

good news for plpgsql developers - enhanced diagnostics fiedls will be available in 9.3

Noah Misch commited my patch, that allows a access to diagnostics fields from PLpgSQL. It can be used for structured custom exception (these fields are accessible on client side).

create or replace function stacked_diagnostics_test() returns void as $$
declare _column_name text;
        _constraint_name text;
        _datatype_name text;
        _table_name text;
        _schema_name text;
begin
  raise exception using
    column = '**some column name**',
    constraint = '**some constraint name**',
    datatype = '**some datatype name**',
    table = '**some table name**',
    schema = '**some schema name**';
exception when others then
  get stacked diagnostics
        _column_name = column_name,
        _constraint_name = constraint_name,
        _datatype_name = pg_datatype_name,
        _table_name = table_name,
        _schema_name = schema_name;
  raise notice 'column %, constraint %, type %, table %, schema %',
    _column_name, _constraint_name, _datatype_name, _table_name, _schema_name;
end;
$$ language plpgsql;


select stacked_diagnostics_test();


NOTICE:  column **some column name**, constraint **some constraint name**, type **some datatype name**, table **some table name**, schema **some schema name**
 stacked_diagnostics_test 
--------------------------
 
(1 row)