Re: Plpgsql - Custom fields Postgres 9.5

On 12/14/2016 05:19 PM, Patrick B wrote:
>
>
> 2016-12-15 14:00 GMT+13:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com
> <mailto: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
> <mailto: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 <mailto: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 =\

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
adrian(dot)klaver(at)aklaver(dot)com