Thread: [Help] AGGREGATE problem w/v7.0

Hi,

Under PostgreSQl 7.0, running the following from a shell script,


#!/sbin/sh

psql mybase << END

CREATE FUNCTION foo(TEXT, TEXT) RETURNS TEXT AS
  'SELECT \$1 || '', '' || \$2'
  LANGUAGE 'sql';

CREATE AGGREGATE foo_a (
  BASETYPE = TEXT,
  SFUNC1 = foo,
  STYPE1 = TEXT,
  INITCOND1 = ''
);

CREATE TEMPORARY TABLE tmp (id TEXT, entry TEXT);
INSERT INTO tmp VALUES('abc', 'kls');
INSERT INTO tmp VALUES('abc', 'mnr');
INSERT INTO tmp VALUES('def', 'opq');
INSERT INTO tmp VALUES('def', 'zyw');

SELECT id, foo_a(entry) FROM tmp GROUP BY id;

END


/why/ does SELECT give up with


ERROR:  SQL-language function not supported in this context.


- Is this a bug/limitation (at least the Programmer's Guide for PostgreSQL
7.0,
  Chapters 4 & 7, doesn't seem to volunteer a clue)?
- What am I doing wrongly?
- What exactly is meant by "this context"?
- How can I rectify this situation (besides hacking up Perl code for my
  purpose, which I finally did)?


Thanks for your advice.

/oskar

"Itzinger, Oskar" <oitzinger@opec.org> writes:
> /why/ does SELECT give up with
> ERROR:  SQL-language function not supported in this context.

The message seems perfectly clear to me ;-)

> - How can I rectify this situation

Update to 7.1:

regression=# SELECT id, foo_a(entry) FROM tmp GROUP BY id;
 id  |   foo_a
-----+------------
 abc | , kls, mnr
 def | , opq, zyw
(2 rows)

Not that I'd recommend a SQL function for an aggregate, mind you.
plpgsql would probably be faster/better.

            regards, tom lane