Postgresql - Postgress how to AUTOMATICALLY left outter join to tables with reference integrity constraints?

user1389698
December 2, 2023
267 views
1 vote
2 Answers

Has anyone written PG_SQL or such method that would generate left outer join queries from reference constraint data only.

Generating these queries, when you need to always takes time, and for most cases this highly mechanical work.

Lets say we have tables ( just as an example to make the question more concrete ).

create table aaa ( id bigint, name varchar);
create table aaa_option( id bigint,aaa_id bigint, name varchar, value varchar);
create table aaa_sub_option( id bigint,option_id bigint, name varchar, value varchar);

CREATE UNIQUE INDEX on aaa(id) ;
CREATE UNIQUE INDEX ON aaa_sub_option(id) ;

ALTER TABLE aaa_option ADD CONSTRAINT aaa_const FOREIGN KEY (aaa_id) REFERENCES aaa(id);
ALTER TABLE aaa_sub_option ADD CONSTRAINT aaa_sub_const FOREIGN KEY (option_id) REFERENCES aaa_sub_option(id);

I am looking for a function like:

rquery('aaa',1);

would give me

select aaa.id as aaa_X_id, aaa.name as aaa_X_name from aaa;

rquery(aaa,2)

would give me:

select aaa.id as aaa_X_id, aaa.name as aaa_X_name,
    X1.id as aaa_option_X_id, X1.aaa_id as aaa_option_X_aaa_id, X1.name as aaa_option_X_name,X1.value as aaa_option_X_value from aaa
  left outer join aaa_option as X1 on aaa.id=X1.aaa_id;

rquery(aaa,3)

would give me:

… to be writen …

Tags: postgresql

Answers

Chosen as BEST ANSWER

I kind of assumed no such thing exists, but wanted to check no-one disagrees on this.

I think I would probably some time to get this done.

     SELECT con.conname,con.contype,rel.relname as relname,UNNEST(conkey) as conkey_ ,rel2.relname,UNNEST(confkey) as confkey_,
            attr.attname,attr.atttypid,attr.attnum,attr2.attname,attr2.atttypid,attr2.attnum
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_class rel2
                       ON rel2.oid = con.confrelid                     
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
            INNER JOIN pg_catalog.pg_attribute attr
                       ON rel.oid=attr.attrelid 
            INNER JOIN pg_catalog.pg_attribute attr2
                       ON rel2.oid=attr2.attrelid                      
       WHERE  contype='f' -- foreign key           
       AND  attr.attnum = ANY ( conkey)
       AND  attr2.attnum = ANY ( confkey)
        ;

Will get neat info

     conname    | contype |    relname     | conkey_ |    relname     | confkey_ |  attname  | atttypid | attnum | attname | atttypid | attnum
---------------+---------+----------------+---------+----------------+----------+-----------+----------+--------+---------+----------+--------
 aaa_const     | f       | aaa_option     |       2 | aaa            |        1 | aaa_id    |       20 |      2 | id      |       20 |      1
 aaa_sub_const | f       | aaa_sub_option |       2 | aaa_sub_option |        1 | option_id |       20 |      2 | id      |       20 |      1
(2 rows)

It looks like all the information I need is there, not that I doubled it would not be accessible.

I would only need to write some PG_PSQL or java to process it into a query generation.

... perhaps I could use recursive querying here.

(Edit)

There’s no such thing, or at least it’s not very common. The use case sounds a bit like what GraphQL is for. In PostgreSQL, you can store and operate on graphs using pgrouting or Apache AGE (your rquery resembles variable-length edge query), but they don’t necessarily structure the graph data the way you do.

You can try those out, or you can even do something like that in vanilla PostgreSQL with a self-referencing table and a recursive query on that: replace the 3 tables with a single one: demo at db<>fiddle

create table aaa_option (
    id bigint generated by default as identity primary key, 
    aaa_parent_id bigint references aaa_option(id),
    name varchar,
    value text);
create index on aaa_option(aaa_parent_id);
insert into aaa_option values
(1,null,'this is a parent, a top-level aaa'              ,null),
(2,1   ,'this is a child, an "aaa_option"'               ,100),
(3,2   ,'this is a child of a child, an "aaa_sub_option"',101);

And then your rquery(N) is this:

prepare rquery(int,text) as 
with recursive cte as (
   select id, 
       aaa_parent_id,
       name,
       value
   from aaa_option a where aaa_parent_id is null
   union all 
   select aaa.id, 
       aaa.aaa_parent_id,
       aaa.name,
       aaa.value
   from cte inner join aaa_option aaa on (aaa.aaa_parent_id=cte.id)
   )cycle id set is_cycle using path
select * from cte where not is_cycle 
  and case $2 when 'eq' then array_length(path,1)=$1
              when 'geq' then array_length(path,1)>=$1
              when 'leq' then array_length(path,1)<=$1
              when 'lt' then array_length(path,1)<$1
              when 'gt' then array_length(path,1)>$1 
              when 'neq' then array_length(path,1)<>$1 
              else true end;

execute rquery(1,'eq');

id	aaa_parent_id	name	value	is_cycle	path
1	null	this is a parent, a top-level aaa	null	f	{(1)}

execute rquery(2,'eq');

id	aaa_parent_id	name	value	is_cycle	path
2	1	this is a child, an "aaa_option"	100	f	{(1),(2)}

execute rquery(3,'leq');

id	aaa_parent_id	name	value	is_cycle	path
1	null	this is a parent, a top-level aaa	null	f	{(1)}
2	1	this is a child, an "aaa_option"	100	f	{(1),(2)}
3	2	this is a child of a child, an "aaa_sub_option"	101	f	{(1),(2),(3)}