CODEWITHSUNDEEP
postgresqlkeyinstances
Maro Torres
Maro Torres

Reputation: 25

Get all instances of primary keys of a table

This is a simple example of what I need, for any given table, I need to get all the instances of the primary keys, this is a little example, but I need a generic way to do it.

create table foo
(
 a numeric
,b text
,c numeric
constraint pk_foo primary key (a,b)
)

insert into foo(a,b,c) values (1,'a',1),(2,'b',2),(3,'c',3);

select <the magical thing>

result 

      a|b
   1 |1|a|
   2 |2|b|
   3 |3|c|
   ..   ...

I need to control if the instances of the primary keys are changed by the user, but I don't want to repeat code in too many tables! I need a generic way to do it, I will put <the magical thing> in a function to put it on a trigger before update and blah blah blah...

Upvotes: 1

Views: 203

Answers (1)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

In PostgreSQL you must always provide a resulting type for a query. However, you can obtain the code of the query you need, and then execute the query from the client:

create or replace function get_key_only_sql(regclass) returns string as $$
select 'select '|| (
          select string_agg(quote_ident(att.attname), ', ' order by col)
          from pg_index i
          join lateral unnest(indkey) col on (true)
          join pg_attribute att on (att.attrelid = i.indrelid and att.attnum = col)
          where i.indrelid = $1 and i.indisprimary
          group by i.indexrelid
          limit 1) || ' from '||$1::text
end;
$$ language sql;

Here's some client pseudocode using the function above:

sql = pgexecscalar("select get_key_only_sql('mytable'::regclass)");
rs = pgopen(sql);

Upvotes: 1

Related Questions