Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Semi-OT: Functions and Aggregates in PostgreSQL

by notsoevil (Pilgrim)
on Oct 16, 2001 at 22:47 UTC ( #119217=perlquestion: print w/replies, xml ) Need Help??

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
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).


CREATE FUNCTION format_link(text,text) RETURNS text AS ' return qq[<a href="$_[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:

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?:
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="$_[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.

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

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="$_[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='');
    Which means the following:
    SELECT get_links(format_links(url,name)) FROM links;
    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
    So that I can just say:
    SELECT * FROM link_list;
    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".

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

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://119217]
Approved by root
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chilling in the Monastery: (3)
As of 2023-12-08 18:34 GMT
Find Nodes?
    Voting Booth?
    What's your preferred 'use VERSION' for new CPAN modules in 2023?

    Results (37 votes). Check out past polls.