I have a function that returns a set of records based on one of my
views. The function takes two arguments of user_id and saved search
name, looks up the search values from a table previously saved and
performs a SELECT query on my view to return my set of records found.
However, I don't want to SELECT all columns of the view in my query,
only the fields that have values specified to search for. So, if a value
in my search table for a clientname field is blank, omit this field from
my query so I can use DISTINCT and pull only those records. I hope this
is not confusing. I guess my question is how to replace the following...
FOR searchresults IN SELECT * FROM my_view WHERE <snip> LOOP
With something like this...
FOR searchresults IN SELECT DISTINCT clientname FROM my_view WHERE <snip> LOOP
But I don't know if the field I want to search is going to be clientname
or clienttype or even other types of fields until I test them for
values. Is there a way for me to construst a comma separated list of
columns to search in my pl/pgsql script and then use it in my FOR LOOP?
I know I can use Perl to help build my list of columns, if needed, but
then how can I pass that off to my SELECT query? If I have a variable
called 'myfields' and try to place that in my SELECT query, it thinks
there is a field named myfields, of course. Maybe a way to evaluate the
comman sepeated list? Thanks in advance for any help! Or suggestion for
a better way to develop user saved searches.
--
Robert