OUPS !!
Things seem to be stuck now, since the DB version is 7.3.2, so no
array_to_string method is available.
Does anyone have any idea how to solve that ?
Regards,
Roger Tannous.
--- Roger Tannous <[EMAIL PROTECTED]> wrote:
> Hi to all, there was a BIG MISTAKE in my proposition regarding my last
> post:
>
> In fact, after examining the online documentation (Note that I don't
> have
> enough experience in postgreSQL !!) I found that
>
> select '(' || replace('1 2', " ", ",") || ')';
>
> could not, in any way, be equivalent to:
>
> select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
>
> in that the first example '1 2' is a string, while indkey is an array
> and
> the later usage of the concatenation operator with the array just
> appends
> strings to the array, which yields an array, not what I expected to be,
> a
> string!! So it's apparently irrelevant to directly use the replace
> command
> with an array !!
>
> In fact, I've also tried:
>
>
> select replace('(\'' || indkey || '\')', " ", "','") from pg_index;
>
> but forgot to mention it in the previous post.
>
> So concatenating any string to an array yields an array... and this
> query
> is irrelevant.
>
> The possible solution would be to convert this array to a string, with
> the
> insertion of the proper quotes and commas; but since the command to be
> used already inserts a delimiter, we can get rid of the replace command.
> Let's see this query now:
>
>
> select '(\'' || array_to_string(indkey, '\',\'') || '\')' from
> pg_index;
>
> I'm sure this should work :)
>
> Now we have the final WHERE statement like this:
>
> WHERE pg_attribute.attnum IN '(\'' || array_to_string(pg_index.indkey,
> '\',\'') || '\')'
>
>
> or ?
>
> WHERE pg_attribute.attnum IN ('\'' || array_to_string(pg_index.indkey,
> '\',\'') || '\'')
>
>
>
> Anyway, I got to test those queries, and I'm optimistic about it.
> Hope they'll work fine :)
>
> Best Regards,
> Roger Tannous.
>
>
>
>
>
>
--
>
--
>
--
>
--
>
> --- Roger Tannous <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> >
> > If you put pg_index.indkey in the select statement, you'd notice that
> > it's
> > sometimes 1 ( it's when we have one PK field) and sometimes 1 2 ( for
> > two
> > PK fields), etc.
> >
> > So I tried to use a replace command like the following:
> >
> > (just to add parentheses, replace the space by a comma to use the
> > resulting string in an IN statement)
> >
> > select '(' || replace('1 2', " ", ",") || ')';
> >
> > which yields: (1,2)
> >
> > But the following query fails to execute!!
> > select replace(indkey, " ", ",") from pg_index;
> >
> > [
> > sub question: Did I miss quotes around elements? I mean should I
> enclose
> > every element originating from the indkey array with single quotes ?
> if
> > yes, so easy, no need to matter about it: so I should have tried the
> > following (which I didn't have time to do yet):
> >
> > select '(\'' || replace(indkey, " ", "','") || '\')' from pg_index;
> >
> > Another issue here too: Could double quotes here be the source of a
> > problem ? So I should have tested also this query:
> >
> > select '(\'' || replace(indkey, ' ', '\',\'') || '\')' from pg_index;
> >
> > I expect this query to work :) Let's hope so!!
> > ]
> >
> >
> >
> > So we can use the following WHERE statement:
> > WHERE pg_attribute.attnum IN '(' || replace('1 2', " ", ",") || ')'
> >
> > which should translate into: WHERE pg_attribute.attnum IN (1,2)
> >
> >
> > Finally, this WHERE statement:
> >
> > WHERE pg_attribute.attnum IN
> > '(\'' || replace(pg_index.indkey, " ", "','") || '\')'
> >
> >
> > [
> > Again, I should test:
> >
> > WHERE pg_attribute.attnum IN
> > '(\'' || replace(pg_index.indkey, ' ', '\',\'') || '\')'
> >
> > ]
> >
> >
> > I wish I had database access in the internet cafe I'm sending this
> > message
> > from :) instead of just loading you with this bunch of questions.
> >
> >
> > Best Regards,
> > Roger Tannous.
> >
> >
> > --- Tom Lane <[EMAIL PROTECTED]> wrote:
> >
> > > "D'Arcy J.M. Cain" writes:
> > > > That's a good question. The following query does this in a very
> > > > unsatisfactory way. Anyone know what the general solution would
> be?
> > >
> > > > ...
> > > > (
> > > > pg_index.indkey[0]=pg_attribute.attnum OR
> > > > pg_index.indkey[1]=pg_attribute.attnum OR
> > > > pg_index.indkey[2]=pg_attribute.attnum OR
> > > > pg_index.indkey[3]=pg_attribute.attnum OR
> > > > pg_index.indkey[4]=pg_attribute.attnum OR
> > > > pg_index.indkey[5]=pg_attribute.attnum OR
> > > > pg_index.indkey[6]=pg_attribute.attnum OR
> > > >
