Re: New array functions

Greg Stark wrote:
> So where are the new array functions and syntaces documented?

Mainly here:
http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
http://developer.postgresql.org/docs/postgres/arrays.html
http://developer.postgresql.org/docs/postgres/functions-array.html
http://developer.postgresql.org/docs/postgres/functions-comparisons.html#AEN12154

> Specifically I want to know how to replace my int_array_aggregate(int) and
> int_array_enum(_int) calls.

I have no idea what those are -- are they from contrib?

You can create an aggregate to turn arbitrary datatype elements into
arrays like this:

CREATE AGGREGATE array_aggregate
(
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);

-- silly example, but what the heck ;-)
regression=# select attrelid, array_aggregate(attnum) from pg_attribute
where attnum > 0 and attnum < 5 group by attrelid limit 3;
attrelid | array_aggregate
----------+-----------------
16639 | {1}
16638 | {1}
17022 | {1,2,3,4}
(3 rows)

If int_array_enum() is supposed to take '{1,2,3}' and produce three
rows, that function was proposed but rejected. Subsequently Peter
Eisentraut pointed out a SQL99 syntax that does this, but I did not get
it done for 7.4. Perhaps for 7.5.

> And how to replace my "arr *= n" calls too.

See:
http://developer.postgresql.org/docs/postgres/functions-comparisons.html#AEN12154

regression=# SELECT g.grosysid, g.groname, s.usesysid, s.usename FROM
pg_shadow s, pg_group g WHERE s.usesysid = any (g.grolist);
grosysid | groname | usesysid | usename
----------+---------+----------+----------
102 | admins | 1 | postgres
100 | grp1 | 100 | user1
101 | grp2 | 100 | user1
100 | grp1 | 101 | user2
100 | grp1 | 102 | user3
101 | grp2 | 102 | user3
102 | admins | 103 | john
(7 rows)

HTH,

Joe