--
-- Primary Keys
--
--
-- View of primary keys
-- andrewsn
--
DROP VIEW pkview;
CREATE VIEW pkview as
SELECT n.nspname AS schema_name,
        c.relname AS table_name,
        c.oid AS table_oid,
        a.attname AS column_name,
        idx.n + 1 AS ordinal_position
   FROM pg_class c, pg_attribute a, pg_index i, pg_namespace n,
        generate_series(0, current_setting('max_index_keys')::integer ) idx(n)
  WHERE c.oid = a.attrelid
    AND c.oid = i.indrelid
    AND i.indisprimary
    AND a.attnum = i.indkey[idx.n]
    AND NOT a.attisdropped
    AND has_schema_privilege(n.oid, 'USAGE'::text)
    AND n.nspname NOT LIKE 'pg!_%' ESCAPE '!'
    AND has_table_privilege(c.oid, 'SELECT'::text)
    AND c.relnamespace = n.oid;

--
-- ispk() Function to tell if a column in a table is a primary key
-- Depends on view of primary keys pkview
-- Overloaded function takes either (schema, table, column)
-- or (table, column) the latter defaulting to public
--
CREATE OR REPLACE FUNCTION ispk(text, text, text)
RETURNS boolean AS
$$
BEGIN
   IF $2 IS NULL OR $3 IS NULL THEN
      RETURN FALSE;
   END IF;
   PERFORM column_name FROM pkview
   WHERE $3 = column_name AND $2 = table_name AND schema_name = coalesce($1,'public'::text);
   IF FOUND THEN
     RETURN TRUE;
   ELSE
     RETURN FALSE;
   END IF;
END;
$$ LANGUAGE 'plpgsql' ;

CREATE OR REPLACE FUNCTION ispk(text, text)
RETURNS boolean AS
$$
   SELECT ispk( NULL, $1, $2 );
$$ LANGUAGE 'sql';

--
-- Alternative Primary Key Set Returning Function
-- Key order is not guaranteed. applejack/davidfetter/elein
--
CREATE OR REPLACE FUNCTION get_pk_cols(text, text, text)
RETURNS SETOF text
LANGUAGE 'sql'
AS $$
SELECT a.attname::text AS "pk_column"
FROM pg_class c JOIN pg_constraint o
   ON ( c.relname = $2 AND c.oid = o.conrelid AND o.contype='p')
   JOIN pg_attribute a ON (a.attrelid = c.oid AND a.attnum = ANY (o.conkey))
JOIN pg_namespace n ON ( n.nspname = coalesce($1,'public'::text) AND c.relnamespace = n.oid)
WHERE NOT a.attisdropped 
    AND has_schema_privilege(n.oid, 'USAGE'::text)
    AND n.nspname NOT LIKE 'pg!_%' ESCAPE '!'
    AND has_table_privilege(c.oid, 'SELECT'::text)
$$ ;

CREATE OR REPLACE FUNCTION get_pk_cols(TEXT)
RETURNS SETOF TEXT
LANGUAGE 'sql'
AS $$
   SELECT get_pk_cols(NULL, $1 );
$$; 

--
-- Primary Key Information from Information Schema 
--
select constraint_name, table_schema, table_name, column_name, ordinal_position 
from key_column_usage
where constraint_name like '%pkey';