Re: Plpgsql - Custom fields Postgres 9.5

[Patrick B <patrickbakerbr@xxxxxxxxx>]

2016-12-15 10:40 GMT+13:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:

On 12/14/2016 01:30 PM, Patrick B wrote:

        1. Why when I run the function manually I get this error?

                select logextract(201612015, 201612015);

                ERROR:  operator does not exist: timestamp without time
        zone >=
                integer

                LINE 13:                 BETWEEN


    The answer is above. Look at your original query at the top of the post.



         I presume this is wrong: _CREATE or REPLACE FUNCTION
        logextract(date_start integer, date_end integer) _- But what
        should I
        use instead?


        <mailto:adrian.klaver@aklaver.com>


You mean the error would be:
select logextract(201612015, 201612015);

When it was supposed to be: select logextract(201611015, 201612015);???

This is not the cause, because it was a mistake when I typed the email.
Even doing:

select logextract(20161115,20161215);


I get same error.

You would. The error is:

ERROR:  operator does not exist: timestamp without time zone >= integer

Change this:


    BETWEEN

                 ' || date_start || '

              AND

                  ' || date_end || '

to

    BETWEEN

       date_start::text

    AND

       date_end::text

Or change the argument types to text and then:

BETWEEN

       date_start

    AND

       date_end

In either case you will have your original query.

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

I tried either changing the argument types:

logextract(date_start text, date_end text)

and also the Between:

 BETWEEN

       date_start::text

    AND

       date_end::text

None of those worked:

ERROR:  function logextract(integer, integer) does not exist

LINE 1: select logextract(20160901,20161001);