Passing parameters gives array error in Postgres 8.2 function

Question

I've got this function

/*
@Function: valiDates [Avoid inserting data from invalid dates]
@Purpose: Providing a _TABLE and a date _COLUMN to be validated.
Given a _DATE (from script name) validate that a certain % of data (_TOLERANCE) belongs to the _INTERVAL of days specified.
*/

CREATE OR REPLACE FUNCTION valiDates(_date date, _table regclass, _column text, _interval integer,  _tolerance real) RETURNS BOOLEAN AS
$$
DECLARE result boolean;
BEGIN
    EXECUTE 'SELECT
            (SELECT count(*) FROM ' || _table::regclass || '
             WHERE ' || _column || ' BETWEEN '''|| _date || '''::date and ''' || _date || '''::date + INTERVAL ''' ||  _interval || 'days'')'
                        || '/
            (SELECT COUNT(*) FROM ' || _table::regclass || ')::real
         > ' || _tolerance
    INTO result;
    RETURN result;
END
;
$$ LANGUAGE plpgsql;

It actually works in my PostgreSQL environment Version 9.1.13, but when I try to call this function on Dev Server (PostgreSQL 8.2) the following error appears:

array value must start with "{" or dimension information

It should work on 8.2 as described in the official documentation page.

This is how I'm calling the function:

select valiDates('2015-03-01','_table','_date',1,0.8);

I really don't know how to fix it, I've already tried calling the function with '_table'::regclass but it doesn't works either.

[Postgres 8.2 is hopelessly outdated.](http://www.postgresql.org/support/versioning/) Why would you have a dev server with long dead and forgotten software versions? The manual page you link to, is for version 8.4, which is also unsupported by now. — Erwin Brandstetter, Mar 20 '15 at 16:41
I'm using Greenplum, which implements a deprecate version of postgres! — Julian Larralde, Mar 20 '15 at 18:20
Then you ***must*** declare that up front. Greenplum is not Postgres. — Erwin Brandstetter, Mar 20 '15 at 18:22

score 0 · Answer 1 · edited May 23 '17 at 12:06

Your error message most probably comes from ~~_date~~ as parameter name. _date is an alias for date[] - the type name for an array of dates. Similar for _interval.
Very old versions of Postgres could misunderstand that first word _date as data type instead of a parameter name. Use a sane parameter name to fix this. It is never a good idea to use basic type names as identifier of any kind.

Your function audited, should work in Postgres 8.2 (untested):

CREATE OR REPLACE FUNCTION validates(_day date, _table regclass, _column text
                                   , _days integer, _tolerance real)
  RETURNS BOOLEAN AS
$func$
DECLARE result boolean;
BEGIN
   EXECUTE 'SELECT
           (SELECT count(*) FROM ' || _table || '
            WHERE ' || quote_ident(_column) || ' BETWEEN '''
            || _day || '''::date and '''
            || _day || '''::date + ' ||  _days
            || ') >
           (SELECT COUNT(*) FROM ' || _table || ') * ' || _tolerance
   INTO result;
   RETURN result;
END
$func$ LANGUAGE plpgsql;

Notes:

Fix parameter names as discussed.
That's nonsense: _table::regclass - _table already is of type regclass
On the other hand, this is suicidal: _column. Wide open for SQL injection. Use quote_ident(_column) instead.
You can just add date + integer to add days. Much simpler.
3 / 2 > 1.5 can be rewritten to 3 > 2 * 1.5. Shorter, cleaner, clearer, avoids rounding error and no need for cast.

Modern syntax

In Postgres 9.1+, this could be:

CREATE OR REPLACE FUNCTION validates(_day date, _tbl regclass, _col text
                                   , _days int, _tolerance real
                                   , OUT result boolean) AS
$func$
BEGIN
   EXECUTE format(
      'SELECT count((%I BETWEEN $1 AND $2) OR NULL) > count(*) * $3 FROM %s'
    , _col, _tbl
    )
   USING _day, _day + _days, _tolerance
   INTO result;
END
$func$ LANGUAGE plpgsql;

Or even cleaner in Postgres 9.4+ with the new aggregate FILTER clause:

'SELECT count(*) FILTER (WHERE %I BETWEEN $1 AND $2)
      > count(*) * $3 FROM %s'

Explanation:

How can I simplify this game statistics query?

Thanks for your quick response Erwin, but the audited function still gives me the same error besides renaming parameters from '_day' to 'param1' (without "_") This is frustrating, damn greenplum implementing such an old version of postgres! — Julian Larralde, Mar 20 '15 at 20:30
@JulianLarralde: Try removing all parameter names and use positional parameters in the body instead: `$1`, `$2`, ... Greenplum built their product on top of Postgres 8.2 quite a few years back and then went their own way from there. I would much rather work with genuine Postgres. — Erwin Brandstetter, Mar 20 '15 at 20:35
@JulianLarralde: And try deleting the old function before you create the new one. If the function signature does not change, parameter names might be retained. — Erwin Brandstetter, Mar 22 '15 at 14:58

score 0 · Answer 2 · answered Mar 25 '15 at 18:01

0

Thank you Erwin for your advice, I took most of it. Finally I realized that it was expecting an array because of the _table parameter (regclass type) I just needed to change it or a string (text).

answered Mar 25 '15 at 18:01

Julian Larralde

9
2

Passing parameters gives array error in Postgres 8.2 function

2 Answers2

Notes:

Modern syntax