Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general