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.
idand they are of typeBIGINT. 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 likecar_to all the columns of the tablecar.