4

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

5
  • It is not Postgres but maybe this will help stackoverflow.com/questions/95967/… Commented Nov 28, 2021 at 17:24
  • 2
    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? Commented Nov 28, 2021 at 17:26
  • 1
    See https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns Commented Nov 28, 2021 at 17:30
  • 1
    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. Commented Nov 28, 2021 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? Commented Nov 28, 2021 at 20:21

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.