Re: [GENERAL] Primary key column numbers...

[Tom Lane]

Justin Tocci <[EMAIL PROTECTED]> writes:
> SELECT indkey
> FROM (SELECT relname, indkey
> FROM pg_catalog.pg_index join pg_catalog.pg_class
>  ON pg_index.indrelid = pg_class.oid
> WHERE indisprimary=true
> UNION
> SELECT viewname, ('{1}')::int2vector[] as indkey
> FROM pg_catalog.pg_views ) t
> WHERE relname = ''

> Result: ERROR: UNION/INTERSECT/EXCEPT could not convert type  
> int2vector[] to int2vector

I think you want just

  SELECT viewname, '1'::int2vector as indkey

for the second arm of the union.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Primary key column numbers...

[Justin Tocci]

What I'm trying to do is
IF the relation given is a TABLE, give the primary key column numbers.
IF the relation is a VIEW, just give an array with a '1' in it.
ELSE NULL.

Later I'll put in some hocus-pocus to be more intelligent about VIEWs  
but right now this would do me fine.


I've read the docs on arrays and such, I just can't seem to put  
together nor cast an array that's compatible with int2vector. There  
was some notice to look at contrib/array but that has been removed in  
version 8, which is what I'm using.


This is what I have so far.

SELECT indkey
FROM (SELECT relname, indkey
FROM pg_catalog.pg_index join pg_catalog.pg_class
ON pg_index.indrelid = pg_class.oid
WHERE indisprimary=true
UNION
SELECT viewname, ('{1}')::int2vector[] as indkey
FROM pg_catalog.pg_views ) t
WHERE relname = ''

Result: ERROR: UNION/INTERSECT/EXCEPT could not convert type  
int2vector[] to int2vector


Any help here would be appreciated.

justin tocci
fort worth, tx

---(end of broadcast)---
TIP 8: explain analyze is your friend