Hi, we have come across a problem where we need an inverted index, an array of IDs ordered by another condition. We came up with this scheme:
-- final inverted index CREATE TABLE product.t_product_inv ( word text primary key not null, ids bigint[] ); -- transition table, "word" contains a single lexeme -- from an original table's "description" field CREATE TABLE product.t_product_inv0 ( word text not null, id bigint not null, price numeric -- not null ); CREATE INDEX t_product_inv0_idx ON product.t_product_inv0 (word, price NULLS FIRST, id); CREATE OR REPLACE FUNCTION array_append_1(bigint[], numeric, bigint) RETURNS bigint[] AS $$select array_append($1, $3)$$ LANGUAGE SQL; CREATE AGGREGATE array_accum_1 (numeric, bigint) ( sfunc = array_append_1, stype = bigint[], initcond = '{}' ); I would like the get the list of IDs ordered by the price field: INSERT INTO product.t_product_inv SELECT word, array_accum_1(price, id) FROM product.t_product_inv0 GROUP BY word ORDER BY word, price NULLS FIRST, id; However, I get an error: ERROR: column "t_product_inv0.price" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: GROUP BY word ORDER BY word, price NULLS FIRST, id; ^ The condition in the error message is true, the field "price" is indeed used in an aggregate function but ignored on purpose in the sfunc. So I obviously didn't expect the error to happen. Is the bug in our approach or in the check for the GROUP BY/ORDER BY/aggregated variables? This was tested on 8.4.1, 8.5CVS from 20090930 and from today. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers