Re: [GENERAL] - Mailing list pgsql-general

On 5/10/2017 7:45 PM, Igor Korot wrote:
> I found this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
> but now I need
> to connect this with information_schema.columns.
>
> What is best way to do it?
>
> Or maybe that query I referenced is completely wrong?


if you're using pg_catalog stuff there's little point in using the
information_schema views, which exist for compatability with the SQL
standard.

information_schema.columns is a view, like...

View definition:
  SELECT current_database()::information_schema.sql_identifier AS
table_catalog,
     nc.nspname::information_schema.sql_identifier AS table_schema,
     c.relname::information_schema.sql_identifier AS table_name,
     a.attname::information_schema.sql_identifier AS column_name,
     a.attnum::information_schema.cardinal_number AS ordinal_position,
     pg_get_expr(ad.adbin,
ad.adrelid)::information_schema.character_data AS column_default,
         CASE
             WHEN a.attnotnull OR t.typtype = 'd'::"char" AND
t.typnotnull THEN 'NO'::text
             ELSE 'YES'::text
         END::information_schema.yes_or_no AS is_nullable,
         CASE
             WHEN t.typtype = 'd'::"char" THEN
             CASE
                 WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
'ARRAY'::text
                 WHEN nbt.nspname = 'pg_catalog'::name THEN
format_type(t.typbasetype, NULL::integer)
                 ELSE 'USER-DEFINED'::text
             END
             ELSE
             CASE
                 WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
'ARRAY'::text
                 WHEN nt.nspname = 'pg_catalog'::name THEN
format_type(a.atttypid, NULL::integer)
                 ELSE 'USER-DEFINED'::text
             END
         END::information_schema.character_data AS data_type,
information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_numb
er AS character_maximum_length,
information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_nu
mber AS character_octet_length,
information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_nu
mber AS numeric_precision,
information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardi
nal_number AS numeric_precision_radix,
information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number
  AS numeric_scale,
information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_n
umber AS datetime_precision,
information_schema._pg_interval_type(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.character_data
AS interval_type,
     NULL::integer::information_schema.cardinal_number AS
interval_precision,
     NULL::character varying::information_schema.sql_identifier AS
character_set_catalog,
     NULL::character varying::information_schema.sql_identifier AS
character_set_schema,
     NULL::character varying::information_schema.sql_identifier AS
character_set_name,
         CASE
             WHEN nco.nspname IS NOT NULL THEN current_database()
             ELSE NULL::name
         END::information_schema.sql_identifier AS collation_catalog,
     nco.nspname::information_schema.sql_identifier AS collation_schema,
     co.collname::information_schema.sql_identifier AS collation_name,
         CASE
             WHEN t.typtype = 'd'::"char" THEN current_database()
             ELSE NULL::name
         END::information_schema.sql_identifier AS domain_catalog,
         CASE
             WHEN t.typtype = 'd'::"char" THEN nt.nspname
             ELSE NULL::name
         END::information_schema.sql_identifier AS domain_schema,
         CASE
             WHEN t.typtype = 'd'::"char" THEN t.typname
             ELSE NULL::name
         END::information_schema.sql_identifier AS domain_name,
     current_database()::information_schema.sql_identifier AS udt_catalog,
     COALESCE(nbt.nspname,
nt.nspname)::information_schema.sql_identifier AS udt_schema,
     COALESCE(bt.typname, t.typname)::information_schema.sql_identifier
AS udt_name,
     NULL::character varying::information_schema.sql_identifier AS
scope_catalog,
     NULL::character varying::information_schema.sql_identifier AS
scope_schema,
     NULL::character varying::information_schema.sql_identifier AS
scope_name,
     NULL::integer::information_schema.cardinal_number AS
maximum_cardinality,
     a.attnum::information_schema.sql_identifier AS dtd_identifier,
     'NO'::character varying::information_schema.yes_or_no AS
is_self_referencing,
     'NO'::character varying::information_schema.yes_or_no AS is_identity,
     NULL::character varying::information_schema.character_data AS
identity_generation,
     NULL::character varying::information_schema.character_data AS
identity_start,
     NULL::character varying::information_schema.character_data AS
identity_increment,
     NULL::character varying::information_schema.character_data AS
identity_maximum,
     NULL::character varying::information_schema.character_data AS
identity_minimum,
     NULL::character varying::information_schema.yes_or_no AS
identity_cycle,
     'NEVER'::character varying::information_schema.character_data AS
is_generated,
     NULL::character varying::information_schema.character_data AS
generation_expression,
         CASE
             WHEN c.relkind = 'r'::"char" OR (c.relkind = ANY
(ARRAY['v'::"char", 'f'::"char"])) AND
pg_column_is_updatable(c.oid::regclass, a.attnum, false) THEN 'YE
S'::text
             ELSE 'NO'::text
         END::information_schema.yes_or_no AS is_updatable
    FROM pg_attribute a
      LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum =
ad.adnum
      JOIN (pg_class c
      JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid
      JOIN (pg_type t
      JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid
      LEFT JOIN (pg_type bt
      JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype =
'd'::"char" AND t.typbasetype = bt.oid
      LEFT JOIN (pg_collation co
      JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON
a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR
co.collname <> 'default'::name)
   WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT
a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char",
'f'::"char"])) AND (pg_has_
role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum,
'SELECT, INSERT, UPDATE, REFERENCES'::text));



--
john r pierce, recycling bits in santa cruz