[GENERAL] pg_dump question (exclude schemas)

[Jay Vee]

I want to backup a database but exclude certain schemas with a patter.

I have 100 schemas with the pattern:  'sch_000', 'sch_001',  and so on.

Will this work?

$pg_dump   --exclude-schema='sch_*'

this does not seem to exclude all schemas with this pattern  ( 'sch_*' ),
anything wrong here?

thanks


J.V.

[GENERAL] trigger or logging

[Jay Vee]

Before running a script (invoked by .sh that will call stored procs which
may invoke other triggers), I want to capture every change made in the
database which includes the field value before the update and the field
value after the update and also capture all inserts.

With this data, I would be able to write a script that would reverse the
database to all previous values.

This may be possible with a trigger or a set of triggers but I would need
some direction here and to think of all scenarios.

before insert ( capture data that will be inserted ),
if the insert ivokes triggers that insert additional rows or updates rows I
would want to capture this.

on any update: trigger to capture the before value and the after value only
on the field values that change.

Any help or examples would be greatly appreciated.

J.V.

[GENERAL] subtracting from a date

[Jay Vee]

I have reviewed working with dates documentation but some things are not
clear and I cannot get an example to work for what I need.

I am passing a date into a stored function like '2013-04-01'

The stored function accepts this string as a date type.

Within the function, I need to:

1.  subtract one year from this date into another date type
2.  subtract one month from this date into another date type
3.  subtract one day from this date into another date type

Are there any examples of this?  This is not a timestamp type, but a date
type.

thanks

Re: [GENERAL] subtracting from a date

[Jay Vee]

I tried that but get an error
v_start_date date;
v_minus_one_year date;

I have v_start_date to start with and want to subtract one year and put
into v_minus_one_year

  select v_start_date - interval '1 yr' as v_minus_one_year;



---  I get:

ERROR:  query has no destination for result data


On Thu, Feb 6, 2014 at 10:31 AM, Adrian Klaver wrote:

> On 02/06/2014 09:25 AM, Jay Vee wrote:
>
>> I have reviewed working with dates documentation but some things are not
>> clear and I cannot get an example to work for what I need.
>>
>> I am passing a date into a stored function like '2013-04-01'
>>
>> The stored function accepts this string as a date type.
>>
>> Within the function, I need to:
>> 1.  subtract one year from this date into another date type
>> 2.  subtract one month from this date into another date type
>> 3.  subtract one day from this date into another date type
>>
>> Are there any examples of this?  This is not a timestamp type, but a
>> date type.
>>
>
> Something like this:
>
> test=> select '2013-04-01'::date - interval '1 yr';
>   ?column?
> -
>  2012-04-01 00:00:00
> (1 row)
>
> test=> select '2013-04-01'::date - interval '1 month';
>   ?column?
> -
>  2013-03-01 00:00:00
> (1 row)
>
> test=> select '2013-04-01'::date - interval '1 day';
>   ?column?
> -
>  2013-03-31 00:00:00
>
>
> You did say what language you are using for the function so the assignment
> will depend on that.
>
>
>> thanks
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>