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. 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
– Erwin Brandstetter, Commented Mar 20, 2015 at 16:41
I'm using Greenplum, which implements a deprecate version of postgres! — Julian Larralde
– Julian Larralde, Commented Mar 20, 2015 at 18:20
Then you must declare that up front. Greenplum is not Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 20, 2015 at 18:22

Community · Accepted Answer · 2017-05-23 12:06:19Z

0

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?

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Mar 20, 2015 at 17:11

Erwin Brandstetter

667k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Julian Larralde Over a year ago

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!

Erwin Brandstetter Over a year ago

@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 Over a year ago

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

Julian Larralde · Accepted Answer · 2015-03-25 18:01:18Z

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, 2015 at 18:01

Julian Larralde

92 bronze badges

Collectives™ on Stack Overflow

Passing parameters gives array error in Postgres 8.2 function

2 Answers 2

Notes:

Modern syntax

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Notes:

Modern syntax

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related