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

user_16763329 0
Viewed 1067

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

postgresql sql
1 Answers

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.

user_80600170
Related