From anthony@developware.com Tue Oct 16 18:24:07 2001 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 From joel@joelburton.com Wed Oct 17 17:49:44 2001 Received: from temp.joelburton.com (temp.joelburton.com [209.190.238.101]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9HHniP96472 for ; Wed, 17 Oct 2001 13:49:44 -0400 (EDT) (envelope-from joel@joelburton.com) Received: by temp.joelburton.com (Postfix, from userid 501) id 17DAF2B9CE; Wed, 17 Oct 2001 13:51:14 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by temp.joelburton.com (Postfix) with ESMTP id 05A9D2B996; Wed, 17 Oct 2001 13:51:14 -0400 (EDT) Date: Wed, 17 Oct 2001 13:51:13 -0400 (EDT) From: Joel Burton To: Anthony Bouvier Cc: Subject: Re: Multiple Parameters to an Aggregate Function In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200110/243 X-Sequence-Number: 5198 On Tue, 16 Oct 2001, Anthony Bouvier wrote: > 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[ class="body_link">$_[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. I have a techdoc about using aggregate functions to create faster web looping at http://www.zope.org/Members/pupq/pg_in_aggregates It was written w/examples in DTML, Zope's scripting language, rather than in Perl/DBI, but you should be able to easily follow it. Essentially, what you want to end up with is something like this: SELECT make_into_li ( make_into_text ( url, name ) ); where make_into_text is the aggregate, and make_into_text is the formatting function. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant