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

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