Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On 12/14/2016 01:17 PM, Patrick B wrote:
> Hi,
>
> I've got this query, that I manually run it once a month:
>
>     SELECT
>         uuid,
>         clientid),
>         *
>     FROM
>         logging
>     WHERE
>         logtime
>     BETWEEN
>         '201611015'
>     AND
>         '201612015'
>
>
>
> As you can see, I select a date. So in December, the date will be:
> *BETWEEN '201612015' AND '201601015'*, for example.
>
> I always need to run this on the 15th of each month.
> I was thinking about creating a PLPGSQL function and a Cron task, so
> this task can be automated.
>
> Also, the file must be saved with the date+.csv. Example:
>
>         CREATE or REPLACE FUNCTION logextract(date_start integer,
>         date_end integer)
>
>             RETURNS void AS $$
>
>             begin
>
>               execute '
>
>               COPY
>
>                   (
>
>                   SELECT
>
>                       uuid,
>
>                       clientid),
>
>                       *
>
>                   FROM
>
>                       logging
>
>                   WHERE
>
>                       logtime
>
>                   BETWEEN
>
>                      ' || date_start || '
>
>                   AND
>
>                       ' || date_end || '
>
>                   )
>
>               TO ''/var/lib/postgresql/'|| date_start ||'_logs.csv''';
>
>             end
>
>             $$ language 'plpgsql';
>
>
>
> *Questions:*
>
> 1. Why when I run the function manually I get this error?
>
>         select logextract(201612015, 201612015);
>
>         ERROR:  operator does not exist: timestamp without time zone >=
>         integer
>
>         LINE 13:                 BETWEEN

The answer is above. Look at your original query at the top of the post.

>
>
>  I presume this is wrong: _CREATE or REPLACE FUNCTION
> logextract(date_start integer, date_end integer) _- But what should I
> use instead?
>
>
> 2. To call the function, I have to login to postgres and then
> run: select logextract(201612015, 201612015);
> How can I do it on cron? because the dates will be different every time.
>
> Thanks
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com