Re: A Table's Primary Key Listing - Mailing list pgsql-sql

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 <roger77_lb@yahoo.com> 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 <roger77_lb@yahoo.com> 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 <tgl@sss.pgh.pa.us> wrote:
> > 
> > > "D'Arcy J.M. Cain" <darcy@druid.net> 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
> > > >       pg_index.indkey[7]=pg_attribute.attnum OR
> > > >       pg_index.indkey[8]=pg_attribute.attnum OR
> > > >       pg_index.indkey[9]=pg_attribute.attnum
> > > >     )
> > > 
> > > In CVS tip you could replace this with "attnum = ANY (indkey)".
> > > Unfortunately, most array support doesn't work on int2vector in
> > > pre-8.1 releases, so I think you're kinda stuck with the above
> > > for now.
> > > 
> > >             regards, tom lane
> > > 
> > 
> > 
> > 
> >         
> > ____________________________________________________
> > Start your day with Yahoo! - make it your home page 
> > http://www.yahoo.com/r/hs 
> >  
> > 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com