Re: 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(dot)klaver(at)aklaver(dot)com