Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Jeff Janes]

On Wed, Dec 14, 2016 at 1:17 PM, Patrick B  wrote:

>
>
> 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.
>

PostgreSQL already knows what date today is.  Why does cron have to tell
it?  Just do 'select logextract()' and let Postgresql compute the dates for
itself.

Cheers,

Jeff

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Adrian Klaver]

On 12/14/2016 05:56 PM, Lucas Possamai wrote:

ERROR:  column "date_start" does not exist


Patrick


Patrick*** - trying on SQL fiddle i got that error when executing what
 Adrian suggested.



Yeah, it was my turn not to be paying attention. It has been that sort 
of day and I guess I could not expect the end of day to get better.


So something that might actually work;

CREATE or REPLACE FUNCTION l_extract(date_start date, date_end date))

RETURNS void AS $$


begin

  execute '

  COPY

  (

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

 $1

  AND

 $2

  )

  TO ''/var/lib/postgresql/'|| date_start ||'_logs.csv'''
  USING date_start, date_end;

end

$$ language 'plpgsql';


select l_extract('20161115'::date, '20161215'::date);

--
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

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

2016-12-15 14:54 GMT+13:00 Lucas Possamai :

>
>
> 2016-12-15 14:34 GMT+13:00 Adrian Klaver :
>
>> On 12/14/2016 05:19 PM, Patrick B wrote:
>>
>> Reading the suggestions might help:)
>>
>> Another try:
>>
>> CREATE or REPLACE FUNCTION l_extract(date_start text, date_end text))
>>
>> 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';
>>
>>
>> select l_extract('201611015', '201612015');
>>
>>
>>
>>
> select l_extract('201611015','201612015');
> ERROR:  column "date_start" does not exist
>
>
> Patrick
>
>


BETWEEN

''' || date_start || '''

AND

''' || date_end || '''


worked!

Thanks David.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Lucas Possamai]

>
> ERROR:  column "date_start" does not exist
>
>
> Patrick
>

Patrick*** - trying on SQL fiddle i got that error when executing what
 Adrian suggested.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Lucas Possamai]

2016-12-15 14:34 GMT+13:00 Adrian Klaver :

> On 12/14/2016 05:19 PM, Patrick B wrote:
>
> Reading the suggestions might help:)
>
> Another try:
>
> CREATE or REPLACE FUNCTION l_extract(date_start text, date_end text))
>
> 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';
>
>
> select l_extract('201611015', '201612015');
>
>
>
>
select l_extract('201611015','201612015');
ERROR:  column "date_start" does not exist


Patrick

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[David G. Johnston]

On Wednesday, December 14, 2016, Patrick B  wrote:

>
>  ' || date_start || '
>
>   AND
>
>   ' || date_end || '
>
>   Results in this

>   BETWEEN
>
> 2016-12-15
>
>   AND
>
>   20160901
>
> Compared to this

>  '2016-12-15'
>
>   AND
>
>   '20160901'
>
>
Your query has single quotes surrounding the values in the between.  In the
the error output they don't.  You need to modify the dynamic SQL so that
they do.

David J.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Adrian Klaver]

On 12/14/2016 05:19 PM, Patrick B wrote:



2016-12-15 14:00 GMT+13:00 David G. Johnston >:

On Wed, Dec 14, 2016 at 5:12 PM, rob stone >wrote:


On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote:
> On Wed, Dec 14, 2016 at 4:49 PM, Patrick B >
> wrote:
> > ERROR:  function logextract(integer, integer) does not exist
> > LINE 1: select logextract(20160901,20161001);
> >
>
> So change the constants you are passing into your function to text
> (i.e., surrounding them with single quotes) so it matches the new
> function signature.
>
> There exists an element of understanding the options you are being
> given and adapting if something basic like this is overlooked.
>
> David J.


1) Have you run a \df+ and made sure the function has been created
correctly?


​It was created originally using integer arguments - and thus was
being called that way.  It was intentionally changed to use "text"
arguments per a suggestion but without any recognition that the call
site needed to change as well - hence the error.​  Running \df+
would give the expected output.  What could be a problem is if the
original function wasn't dropped so while the text arg'd one was
created the actual call would still reference the old int arg'd
version and any changes would not appear to have been made.


2) In your first post there is a single apostrophe after the execute
instruction. Can't see the closing apostrophe but then my
eyesight is
not the best.


​I'd recommend using the "format" function but last time I did that
the person I way trying to help got mad...​


3) I've always found it easier to TO_CHAR a date column when
using it
for comparison purposes.


​I'm not following this "use text" approach at all...​I get the
logistics but PostgreSQL allows for comparison of date typed data...

David J.



I've done:

1. Deleted all the functions;
2. Created a new function:

 CREATE or REPLACE FUNCTION l_extract(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';


3. Calling the function:

select l_extract('20160901');

select l_extract('2016-09-01'); --> doesn't work either


4. Error:

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.

QUERY:

  COPY

  (

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

2016-12-15

  AND

  20160901

  )

  TO '/var/lib/postgresql/2016-12-15_logs.csv'

CONTEXT:  PL/pgSQL function iknock_log_extract(text) line 7
at EXECUTE


5. \d+ logging:

log_time   | timestamp(3) without time zone


6. Query below works:

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

 '2016-12-15'

  AND

  '20160901'


Still can't understand what's going on =\


Reading the suggestions might help:)

Another try:

CREATE or REPLACE FUNCTION l_extract(date_start text, date_end text))

RETURNS void AS $$


begin

  execute '

  COPY

  (

  SELECT

  uuid,

  

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[armand pirvu]

I presume you point at me. Keep the record straight. I got mad not for the help 
but for the high horse attitude. 
We all have good and bad. No one is perfect and no one deserves this crap

Sent from my iPhone

> On Dec 14, 2016, at 7:19 PM, Patrick B  wrote:
> 
> 
> 
> 2016-12-15 14:00 GMT+13:00 David G. Johnston :
>>> On Wed, Dec 14, 2016 at 5:12 PM, rob stone  wrote:
>> 
>>> 
>>> On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote:
>>> > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B 
>>> > wrote:
>>> > > ERROR:  function logextract(integer, integer) does not exist
>>> > > LINE 1: select logextract(20160901,20161001);
>>> > >
>>> >
>>> > So change the constants you are passing into your function to text
>>> > (i.e., surrounding them with single quotes) so it matches the new
>>> > function signature.
>>> >
>>> > There exists an element of understanding the options you are being
>>> > given and adapting if something basic like this is overlooked.
>>> >
>>> > David J.
>>> 
>>> 
>>> 1) Have you run a \df+ and made sure the function has been created
>>> correctly?
>> 
>> ​It was created originally using integer arguments - and thus was being 
>> called that way.  It was intentionally changed to use "text" arguments per a 
>> suggestion but without any recognition that the call site needed to change 
>> as well - hence the error.​  Running \df+ would give the expected output.  
>> What could be a problem is if the original function wasn't dropped so while 
>> the text arg'd one was created the actual call would still reference the old 
>> int arg'd version and any changes would not appear to have been made.
>> 
>>> 
>>> 2) In your first post there is a single apostrophe after the execute
>>> instruction. Can't see the closing apostrophe but then my eyesight is
>>> not the best.
>> 
>> ​I'd recommend using the "format" function but last time I did that the 
>> person I way trying to help got mad...​
>> 
>>> 
>>> 3) I've always found it easier to TO_CHAR a date column when using it
>>> for comparison purposes.
>> 
>> ​I'm not following this "use text" approach at all...​I get the logistics 
>> but PostgreSQL allows for comparison of date typed data...
>> 
>> David J.
>> 
> 
> 
> I've done:
> 
> 1. Deleted all the functions;
> 2. Created a new function:
> 
>  CREATE or REPLACE FUNCTION l_extract(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';
> 
> 3. Calling the function:
> select l_extract('20160901');
> 
> select l_extract('2016-09-01'); --> doesn't work either
> 
> 
> 
> 4. Error:
> 
> 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.
> 
> QUERY:  
> 
>   COPY 
>   (
>   SELECT
>   uuid,
>   clientid,
>   *
>   FROM
>   logging
>   WHERE
>   logtime
>   BETWEEN
> 2016-12-15
>   AND
>   20160901
>   )
> 
>   TO '/var/lib/postgresql/2016-12-15_logs.csv'
> 
> CONTEXT:  PL/pgSQL function iknock_log_extract(text) line 7 at EXECUTE
> 
> 
> 
> 
> 
> 5. \d+ logging:
> 
> log_time   | timestamp(3) without time zone
> 
> 
> 
> 6. Query below works:
> 
>   SELECT
> 
>   uuid,
> 
>   clientid,
> 
>   *
> 
>   FROM
> 
>   logging
> 
>   WHERE
> 
>   logtime
> 
>   BETWEEN
> 
>  '2016-12-15'
> 
>   AND
> 
>   '20160901'
> 
> 
> 
> Still can't understand what's going on =\
> 
> 

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

2016-12-15 14:00 GMT+13:00 David G. Johnston :

> On Wed, Dec 14, 2016 at 5:12 PM, rob stone  wrote:
>
>>
>> On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote:
>> > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B 
>> > wrote:
>> > > ERROR:  function logextract(integer, integer) does not exist
>> > > LINE 1: select logextract(20160901,20161001);
>> > >
>> >
>> > So change the constants you are passing into your function to text
>> > (i.e., surrounding them with single quotes) so it matches the new
>> > function signature.
>> >
>> > There exists an element of understanding the options you are being
>> > given and adapting if something basic like this is overlooked.
>> >
>> > David J.
>>
>>
>> 1) Have you run a \df+ and made sure the function has been created
>> correctly?
>>
>
> ​It was created originally using integer arguments - and thus was being
> called that way.  It was intentionally changed to use "text" arguments per
> a suggestion but without any recognition that the call site needed to
> change as well - hence the error.​  Running \df+ would give the expected
> output.  What could be a problem is if the original function wasn't dropped
> so while the text arg'd one was created the actual call would still
> reference the old int arg'd version and any changes would not appear to
> have been made.
>
>
>> 2) In your first post there is a single apostrophe after the execute
>> instruction. Can't see the closing apostrophe but then my eyesight is
>> not the best.
>>
>
> ​I'd recommend using the "format" function but last time I did that the
> person I way trying to help got mad...​
>
>
>> 3) I've always found it easier to TO_CHAR a date column when using it
>> for comparison purposes.
>>
>
> ​I'm not following this "use text" approach at all...​I get the logistics
> but PostgreSQL allows for comparison of date typed data...
>
> David J.
>
>

I've done:

1. Deleted all the functions;
2. Created a new function:

 CREATE or REPLACE FUNCTION l_extract(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';


3. Calling the function:

select l_extract('20160901');

select l_extract('2016-09-01'); --> doesn't work either


4. Error:

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.

QUERY:

  COPY

  (

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

2016-12-15

  AND

  20160901

  )

  TO '/var/lib/postgresql/2016-12-15_logs.csv'

CONTEXT:  PL/pgSQL function iknock_log_extract(text) line 7 at EXECUTE


5. \d+ logging:

log_time   | timestamp(3) without time zone


6. Query below works:

  SELECT

  uuid,

  clientid,

  *

  FROM

  logging

  WHERE

  logtime

  BETWEEN

 '2016-12-15'

  AND

  '20160901'


Still can't understand what's going on =\

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[David G. Johnston]

On Wed, Dec 14, 2016 at 5:12 PM, rob stone  wrote:

>
> On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote:
> > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B 
> > wrote:
> > > ERROR:  function logextract(integer, integer) does not exist
> > > LINE 1: select logextract(20160901,20161001);
> > >
> >
> > So change the constants you are passing into your function to text
> > (i.e., surrounding them with single quotes) so it matches the new
> > function signature.
> >
> > There exists an element of understanding the options you are being
> > given and adapting if something basic like this is overlooked.
> >
> > David J.
>
>
> 1) Have you run a \df+ and made sure the function has been created
> correctly?
>

​It was created originally using integer arguments - and thus was being
called that way.  It was intentionally changed to use "text" arguments per
a suggestion but without any recognition that the call site needed to
change as well - hence the error.​  Running \df+ would give the expected
output.  What could be a problem is if the original function wasn't dropped
so while the text arg'd one was created the actual call would still
reference the old int arg'd version and any changes would not appear to
have been made.


> 2) In your first post there is a single apostrophe after the execute
> instruction. Can't see the closing apostrophe but then my eyesight is
> not the best.
>

​I'd recommend using the "format" function but last time I did that the
person I way trying to help got mad...​


> 3) I've always found it easier to TO_CHAR a date column when using it
> for comparison purposes.
>

​I'm not following this "use text" approach at all...​I get the logistics
but PostgreSQL allows for comparison of date typed data...

David J.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[rob stone]

On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote:
> On Wed, Dec 14, 2016 at 4:49 PM, Patrick B 
> wrote:
> > ERROR:  function logextract(integer, integer) does not exist
> > LINE 1: select logextract(20160901,20161001);
> > 
> 
> So change the constants you are passing into your function to text
> (i.e., surrounding them with single quotes) so it matches the new
> function signature.
> 
> There exists an element of understanding the options you are being
> given and adapting if something basic like this is overlooked.
> 
> David J.


1) Have you run a \df+ and made sure the function has been created
correctly?

2) In your first post there is a single apostrophe after the execute
instruction. Can't see the closing apostrophe but then my eyesight is
not the best.

3) I've always found it easier to TO_CHAR a date column when using it
for comparison purposes.

HTH.
Rob


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

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[David G. Johnston]

On Wed, Dec 14, 2016 at 4:49 PM, Patrick B  wrote:

>
ERROR:  function logextract(integer, integer) does not exist
>
> LINE 1: select logextract(20160901,20161001);
>
So change the constants you are passing into your function to text (i.e.,
surrounding them with single quotes) so it matches the new function
signature.

There exists an element of understanding the options you are being given
and adapting if something basic like this is overlooked.

David J.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

2016-12-15 10:40 GMT+13:00 Adrian Klaver :

> On 12/14/2016 01:30 PM, Patrick B wrote:
>
>> 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?
>>
>>
>> 
>>
>>
>> You mean the error would be:
>> select logextract(201612015, 201612015);
>>
>> When it was supposed to be: select logextract(201611015, 201612015);???
>>
>> This is not the cause, because it was a mistake when I typed the email.
>> Even doing:
>>
>> select logextract(20161115,20161215);
>>
>>
>> I get same error.
>>
>
> You would. The error is:
>
> ERROR:  operator does not exist: timestamp without time zone >= integer
>
> Change this:
>
>
> BETWEEN
>
>  ' || date_start || '
>
>   AND
>
>   ' || date_end || '
>
> to
>
> BETWEEN
>
>date_start::text
>
> AND
>
>date_end::text
>
> Or change the argument types to text and then:
>
> BETWEEN
>
>date_start
>
> AND
>
>date_end
>
> In either case you will have your original query.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com



I tried either changing the argument types:

logextract(date_start text, date_end text)


and also the Between:

 BETWEEN


   date_start::text


AND


   date_end::text


None of those worked:

ERROR:  function logextract(integer, integer) does not exist

LINE 1: select logextract(20160901,20161001);

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Adrian Klaver]

On 12/14/2016 01:30 PM, Patrick B wrote:

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?





You mean the error would be:
select logextract(201612015, 201612015);

When it was supposed to be: select logextract(201611015, 201612015);???

This is not the cause, because it was a mistake when I typed the email.
Even doing:

select logextract(20161115,20161215);


I get same error.


You would. The error is:

ERROR:  operator does not exist: timestamp without time zone >= integer

Change this:


BETWEEN

 ' || date_start || '

  AND

  ' || date_end || '

to

BETWEEN

   date_start::text

AND

   date_end::text

Or change the argument types to text and then:

BETWEEN

   date_start

AND

   date_end

In either case you will have your original query.

--
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

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[David G. Johnston]

On Wed, Dec 14, 2016 at 2:17 PM, Patrick B  wrote:

>
> As you can see, I select a date. So in December, the date will be: *BETWEEN
> '201612015' AND '201601015'*, for example.
>
>
​That is an unusual timestamp value...what's the 5 for?​ (I've figured this
out...but its still unusual)

>
>
> 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
>
>
>  I presume this is wrong: *CREATE or REPLACE FUNCTION
> logextract(date_start integer, date_end integer) *- But what should I use
> instead?
>
>
I don't understand why "date" wouldn't be your first choice here.​  Or,
better yet, a single argument of type daterange.


> 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.
>
>
​PostgreSQL knows what the current date is so describe how to compute your
desired boundaries given a single date.
​
Dates and times are their own types in PostgreSQL.  They are incompatible
with integers.  You either to convert one or the other if you want to
perform a comparison.

David J.

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

[Patrick B]

>
> 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?
>>
>>
>> 
>>
>
You mean the error would be:
select logextract(201612015, 201612015);

When it was supposed to be: select logextract(201611015, 201612015);???

This is not the cause, because it was a mistake when I typed the email.
Even doing:

select logextract(20161115,20161215);

I get same error.

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