From: | Robert Fitzpatrick <lists(at)webtent(dot)net> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Constructing a SELECT statement in pl/pgsql |
Date: | 2007-05-16 21:33:13 |
Message-ID: | 1179351193.5285.18.camel@columbus.webtent.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2007-05-16 21:36:47 | Re: Postgres Printed Manuals |
Previous Message | Vivek Khera | 2007-05-16 21:04:01 | Re: Performance issues of one vs. two split tables. |