Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5 - Mailing list pgsql-general

On 12/14/2016 05:19 PM, Patrick B wrote:
>
>
> 2016-12-15 14:00 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com
> <mailto:david.g.johnston@gmail.com>>:
>
>     On Wed, Dec 14, 2016 at 5:12 PM, rob stone <floriparob@gmail.com
>     <mailto:floriparob@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 <patrickbakerbr@gmail.com <mailto:patrickbakerbr@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 =\

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.klaver@aklaver.com