Re: Plpgsql - Custom fields Postgres 9.5

2016-12-15 14:00 GMT+13:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> On Wed, Dec 14, 2016 at 5:12 PM, rob stone <floriparob(at)gmail(dot)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 <patrickbakerbr(at)gmail(dot)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 =\