Multiple Parameters to an Aggregate Function
От | Anthony Bouvier |
---|---|
Тема | Multiple Parameters to an Aggregate Function |
Дата | |
Msg-id | CBEBKIKGCCOCPDEMKBBJMEBCCAAA.anthony@developware.com обсуждение исходный текст |
Ответы |
Re: Multiple Parameters to an Aggregate Function
|
Список | pgsql-sql |
I have a sub in a Perl script that loops execution the following statement: SELECT url,name FROM links And formats it like so: <a href='$url'>$name</a><br> 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[<a href="http://www.domain.com/$_[0]" class="body_link">$_[1]</a>]; ' 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[<a href="http://www.domain.com/$_[0]" class="body_link">$_[1]</a><br>]; ' 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
В списке pgsql-sql по дате отправления: