Received: from dathon.com (ideas.currentmarketing.com [216.26.151.48] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9GIO7P26018 for ; Tue, 16 Oct 2001 14:24:07 -0400 (EDT) (envelope-from anthony@developware.com) Received: from nyartlathotep ([216.253.89.194]) by dathon.com (8.12.0/8.12.0) with SMTP id f9GIMPN7026189 for ; Tue, 16 Oct 2001 14:22:25 -0400 From: "Anthony Bouvier" To: Subject: Multiple Parameters to an Aggregate Function Date: Tue, 16 Oct 2001 14:23:43 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200110/230 X-Sequence-Number: 5185 I have a sub in a Perl script that loops execution the following statement: SELECT url,name FROM links And formats it like so: $name
A variable ($link_list) is in the loop, holding the concatenated last statement with the new one. I would rather do this with FUNCTIONS (and/or AGGREGATES). So, I CREATEd a FUNCTION: CREATE FUNCTION format_link(text,text) RETURNS text AS ' return qq[$_[1]]; ' LANGUAGE 'plperl'; So in my Perl script, the SQL statement becomes: SELECT format_link(url,name) FROM links However, I still have to loop with Perl -- it would be nice to use an AGGREGATE to do some of this for me. I can create an AGGREGATE but from what I can tell, the format of the sfunc can only have two parameters like so: sfunc_name(type,type) Where the first parameter is what was passed before, and the second parameter is the 'new' info to do stuff to. Is it not possible to do something similar to?: sfunc_name(type,type,type) So that I can pass the url and name to the AGGREGATE (so it can in turn pass it to the sfunc)? Where the sfunc could be something like so: CREATE FUNCTION link_agg (text,text,text) RETURNS text AS ' return $_[0] . qq[$_[1]
]; ' LANGUAGE 'plperl'; Because then I gain benefit of a stored procedure and cut the SQL in the script down to: SELECT link_agg(url,name) FROM link; Which will return the entire list at once, instead of needing the script to loop through multiple fetches. ... Of course, I may be going about this in an entirely incorrect manner. Telling me so, with a bit of direction, would also be greatly appreciated. BTW: I tried searching the archives, but there is a database error ("PQconnectPoll() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'db.hub.org' and accepting connections on TCP/IP port 5439?"), just so someone knows. Thanks In Advance, Anthony Bouvier