How to get primary key's column values in SQL query (PostgreSQL)

Question

I'm making a function to obtain all values for the primary key's column of my tables. The function receives the name of the table where I want to work. The problem is that tables have different primary key names, so I can't just do

SELECT car_id FROM table   /* receiving "car" as the table*/

Because it would change if I receive the table "boat".

So, what I'm trying to do is something like

SELECT primary_key FROM received table

The query is stored in a character varying where I would insert the received table name and then execute it

It is not Postgres but maybe this will help https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table — Florian Becker, Nov 28 '21 at 17:24
This design pattern has some alarm bells associated with it and you are going to have to dynamically build the sql string to execute by querying the system tables for the appropriate key column name, which *may* be an issue within a function. What would you want to do in the case of a composite key? — Stu, Nov 28 '21 at 17:26
See [https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns](https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns) — Stu, Nov 28 '21 at 17:30
Just a note. Most of the primary keys of my tables are called `id` and they are of type `BIGINT`. Sometimes the exception are relation tables, but it turns out this is not the case too often anymore. There's little reason to prepend something like `car_` to all the columns of the table `car`. — The Impaler, Nov 28 '21 at 19:32
What is the output if there's more than one primary key column? Or is that never the case for your tables? — Bohemian, Nov 28 '21 at 20:21

Edouard · Answer 1 · 2021-11-30T08:07:54.327

As stated by @Stu you can create a dynamic query based on a string which represents the list of the primary key columns of the table whose name is passed to a function which returns the set of values for these primary key columns :

CREATE OR REPLACE FUNCTION keycolumns_values (IN table_name text)
RETURNS setof record LANGUAGE plpgsql AS
$$
DECLARE
  primarykey_columns text ;
BEGIN
-- build the list of primary key columns
SELECT string_agg(a.attname, ',' ORDER BY a.attname)
  INTO primarykey_columns
  FROM pg_index i
  JOIN pg_attribute a
    ON a.attrelid = i.indrelid
   AND a.attnum = ANY(i.indkey)
 WHERE i.indrelid = quote_ident(table_name) ::regclass
   AND i.indisprimary; 
-- dynamic query to return the values of the primary key columns
RETURN QUERY EXECUTE 
'SELECT ' || primarykey_columns ' FROM ' || quote_ident(table_name);
END ;
$$ ;

The issue is that when calling this function :

SELECT * FROM keycolumns_values (your_table_name)

you get the error :

error: a column definition list is required for functions returning "record"

In order to define the column list returned by the keycolumns_values function, I propose to create a set of composite types, one per table, whose content corresponds to the primary key, and whose name is the table name preceded by 'pk_' (call this procedure just once) :

CREATE OR REPLACE PROCEDURE create_pk_types ()
LANGUAGE plpgsql AS
$$
DECLARE
rec record ;
BEGIN
    FOR rec IN
    SELECT t.table_name, string_agg(a.attname || ' ' || format_type(a.atttypid, a.atttypmod), ',' ORDER BY a.attname) AS pk_list
    FROM information_schema.tables AS t
    JOIN pg_index i
      ON i.indrelid = t.table_name::regclass
    JOIN pg_attribute a
      ON a.attrelid = i.indrelid
     AND a.attnum = ANY(i.indkey)
    WHERE t.table_schema='public'
    AND t.table_type='BASE TABLE'
    AND i.indisprimary
    GROUP BY t.table_name
    LOOP
        EXECUTE format('DROP TYPE IF EXISTS pk_%s', rec.table_name) ;
        EXECUTE format('CREATE TYPE pk_%s AS (%s)', rec.table_name, rec.pk_list) ;
    END LOOP ;
END ;
$$ ;

CALL create_pk_types () ;

Then update the dynamic query of the keycolumns_values function so that to integrate the right composite type :

RETURN QUERY EXECUTE 
'SELECT row(' || primarykey_columns || ') :: pk_' || table_name || ' FROM ' || quote_ident(table_name);

and finally, the following query should provide the expected result for the table car

SELECT (x.y).* FROM keycolumns_values('car') AS x(y pk_car)

and the same for any table with or without a composite primary key.

This solution may be considered as quite complex. Any idea to simplify it or make it smarter will be appreciated.

How to get primary key's column values in SQL query (PostgreSQL)

1 Answers1