2016-12-15 13:38 GMT+13:00 Patrick B <[email protected]>:
> Olá,
>
> Eu possuo a seguinte query:
>
> COPY (
>
> SELECT
>
> uuid,
>
> clientid,
>
> *
>
> FROM
>
> logging
>
> WHERE
>
> logtime
>
> BETWEEN
>
> '201611015'
>
> AND
>
> '201612015' ) TO '/var/lib/postgresql/arquivo.csv';
>
>
> Esta query tem que ser manualmente rodada 1x por mês. Por esta razão,
> estou fazendo uma PLPGSQL function para que isso seja automatizado.
>
> CREATE or REPLACE FUNCTION lextract(date_end text)
>
> RETURNS void AS $$
>
>
> DECLARE
>
> date_start date := CURRENT_DATE;
>
>
> 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';
>
>
>
> Quando chamo a function, recebo este erro:
>
> select lextract('201611015');
>
> ERROR: operator does not exist: timestamp without time zone >= integer
>
> LINE 13: BETWEEN
>
> ^
>
> HINT: No operator matches the given name and argument type(s). You might
>> need to add explicit type casts.
>
>
> No que estou falhando? Obrigado!
>
Consegui resolver da seguinte forma:
BETWEEN
''' || date_start || '''
AND
''' || date_end || '''
Obrigado.
Patrick
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
