2016-12-15 14:00 GMT+13:00 David G. Johnston <david.g.johns...@gmail.com>:
> On Wed, Dec 14, 2016 at 5:12 PM, rob stone <floripa...@gmail.com> 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 <patrickbake...@gmail.com>
>> > 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 =\
