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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
