Re: Is This A Set Based Solution?

On Mon, 12 Mar 2007 10:41:21 -0400, tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
in <15437(dot)1173710481(at)sss(dot)pgh(dot)pa(dot)us>

>Stefan Berglund <sorry(dot)no(dot)koolaid(at)for(dot)me> writes:
>> On Sat, 10 Mar 2007 00:37:08 -0500, tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
>>> It looks pretty ugly to me too, but you haven't explained your problem
>>> clearly enough for anyone to be able to recommend a better solution path.
>>> Why do you feel you need to do this? What is the context?
>
>> What I want to do is to create a function that takes a comma separated
>> string of numbers and produces a table (where each row is one of those
>> numbers) that can be joined to other tables as in the example first
>> provided.
>
>That was what you said before. The question is why you need to do that.
>It strikes me that having such a requirement is a symptom of poor data
>representation choices. Perhaps an array would be better, or maybe you
>ought to refactor your table layout altogether. But, as I said, you
>haven't provided any info that would let someone give advice at that
>level.

Perhaps it is a case of poor data representation choices and that is
exactly why I posted originally - because I wasn't sure if that was the
best way of doing what I want to do:

I have an app where the user makes multiple selections from a list. I
can either construct a huge WHERE clause such as SELECT blah blah FROM
foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
alternatively pass the string of IDs ('53016,27,292,512') to a table
returning function which TABLE is then JOINed with the table I wish to
query instead of using the unwieldy WHERE clause. The latter strikes me
as a far more scalable method since it eliminates having to use dynamic
SQL to construct the ridiculously long WHERE clause which will no doubt
ultimately bump up against parser length restrictions or some such.

I didn't find any examples that showed JOINing an array with a table.
How do other developers solve this basic problem and why does my
approach seem so foreign?

SELECT blah blah
FROM
fn_Split_List('53016,27,292,512') SL INNER JOIN
foo T ON SL.N=T.ID;

or

SELECT blah blah
FROM foo
WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512)
---
Stefan Berglund