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
Well, I've seem to have found an answer to my own question. Isn't funny how one can explain a coding problem to a non-technical person (eg: my loving wife), and somehow they give you an answer simply by listening? 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] Which means the following: `SELECT get_links(format_links(url,name)) FROM links;` [download] Will yield my needed results. I'll probably abstract with a VIEW: `CREATE VIEW link_list AS SELECT get_links(format_links(url,name)) FROM links` [download] So that I can just say: `SELECT * FROM link_list;` [download] Hopefully my original question and now this reply will be useful to some other monk (or aspiring monk) later on -- even if it is "Semi-OT". -- notsoevil -- Jeremiah 49:32 - And their camels shall be a booty. . .	[reply] [d/l] [select]

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]