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

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