On 12/14/2016 05:19 PM, Patrick B wrote:
2016-12-15 14:00 GMT+13:00 David G. Johnston <[email protected]
<mailto:[email protected]>>:
On Wed, Dec 14, 2016 at 5:12 PM, rob stone <[email protected]
<mailto:[email protected]>>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 <[email protected]
<mailto:[email protected]>>
> 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,
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');
--
Adrian Klaver
[email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
