Semi-OT: Functions and Aggregates in PostgreSQL

notsoevil has asked for the wisdom of the Perl Monks concerning the following question:

You've probably noticed the topic is "Semi-OT". That's there because I am conflicted. My problem is not entirely centered about Perl (I used Perl as a Procedural language in PostgreSQL), but I have gotten to know that there is a breadth of knowledge here on other topics as well, due to the real-life experiences of my fellow monks. Now I am faced with a problem that may have been experienced by some of you out there, but I have no other effecient way of presenting you with it for advice.

So, I'll post here not seeking Perl Wisdom specifically, but seeking Wisdom of the Monks:

I have a sub in a Perl script that loops fetching the following statement (prepare once, execute once, a while loop for fetchrow_array)

  SELECT url,name FROM links
[download]

And formats it like so:

  <a href='$url'>$name</a><br>
[download]

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';
[download]

So in my Perl script, the SQL statement becomes:

  SELECT format_link(url,name) FROM links
[download]

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)
[download]

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)
[download]

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';
[download]

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;
[download]

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.

--
notsoevil
--
Jeremiah 49:32 - And their camels shall be a booty. . .

Comment on Semi-OT: Functions and Aggregates in PostgreSQL Select or Download Code

Replies are listed 'Best First'.

Re: Semi-OT: Functions and Aggregates in PostgreSQL
by notsoevil (Pilgrim) on Oct 19, 2001 at 00:00 UTC

Not totally tested yet (I need to 'greek' the database first), but cursory inspection leads me to believe this is what I wanted in the first place.

CREATE FUNCTION format_links(text,text)
RETURNS text AS '
  return qq[<a href="http://www.domain.com/$_[0]">$_[1]</a>];
' LANGUAGE 'plperl';

CREATE FUNCTION links_agg(text,text)
RETURNS text AS '
  if ($_[0]) {
    return $_[0] . qq[<br>] . $_[1];
  }
  else {
    return $_[1];
  }
' LANGUAGE 'plperl';

CREATE AGGREGATE get_links (basetype=text, sfunc=links_agg, stype=text
+, initcond='');
[download]

SELECT get_links(format_links(url,name)) FROM links;
[download]

CREATE VIEW link_list AS
SELECT get_links(format_links(url,name))
FROM links
[download]

SELECT * FROM link_list;
[download]

--
notsoevil
--
Jeremiah 49:32 - And their camels shall be a booty. . .

[reply]
[d/l]
[select]


more useful options
	PerlMonks