Thread: plpgsql setof help

Hello, I'm trying to write a pl/pgsql function that takes in a column 
(setof?) of text values and returns an english language list, for example:

if "select towns from towns_table;" retuned
town1
town2
town3

I want the following:
select column_to_english_list( select towns from towns_table );

to return:
'town1, town2 and town3'

In order to do this, I think I would have to create a pl/pgsql function 
that accpts a setof text argument, but I'm not sure that's allowed.  
Anyone care to comment?

Thank you,

Matthew O'Connor

"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I want the following:
> select column_to_english_list( select towns from towns_table );

> to return:
> 'town1, town2 and town3'

> In order to do this, I think I would have to create a pl/pgsql function 
> that accpts a setof text argument, but I'm not sure that's allowed.  

Well, if you are okay with writing

select column_to_english_list( 'select towns from towns_table' );

(ie, pass the subquery as a string) then you could do something
involving FOR ... IN EXECUTE $1.

I wonder though if it wouldn't be better to recast the problem as an
aggregate:

select column_to_english_list(towns) from towns_table;

probably using text[] as the transition state, and having the
transition function just accumulate all the words into the array
and then the final function decides where to plaster commas and
"and"s.

You can find examples of similar aggregates in the PG archives, IIRC.
        regards, tom lane

Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
>> I want the following:
>> select column_to_english_list( select towns from towns_table );
> 
>> to return:
>> 'town1, town2 and town3'
> 
> I wonder though if it wouldn't be better to recast the problem as an
> aggregate:
> 
> select column_to_english_list(towns) from towns_table;
> 
> probably using text[] as the transition state, and having the
> transition function just accumulate all the words into the array
> and then the final function decides where to plaster commas and
> "and"s.
> 
> You can find examples of similar aggregates in the PG archives, IIRC.


Actually; an example is in docs:

http://www.postgresql.org/docs/8.3/static/xaggr.html

use array_accum(); then reformat final text (to remove {} and change 
last comma to 'and').