PostgreSQL Hierarchical Relationship Functions

CREATE TABLE sectors
(
    sector_id integer PRIMARY KEY,
    sector_name varchar(100) NOT NULL,
    parent_sector_id integer REFERENCES sectors(sector_id)
);

INSERT INTO sectors(sector_id, sector_name, parent_sector_id)
SELECT 1, 'All sectors', NULL UNION ALL
SELECT 2, 'Business', 1 UNION ALL
SELECT 3, 'Manufacturing', 2 UNION ALL
SELECT 4, 'Retail', 2 UNION ALL
SELECT 5, 'Trading', 1 UNION ALL
SELECT 6, 'Non开发者_如何学Goprofit', 1 UNION ALL
SELECT 7, 'Agriculture', 1;

Question 1: Find parent relationship via function

SELECT is_parent(1/*All sectors*/, 4/*Retail*/) should be true
SELECT is_parent(2/*Business*/, 4/*Retail*/) should be true

Question 2: Find child relationship via function

SELECT is_child(4/*Retail*/, 1/*All sectors*/) should be true
SELECT is_child(4/*Retail*/, 2/*Business*/) should be true

Any help on this would be highly appreciated.

If you really need to answer that kind of queries, I would recommend you to take a look at PostgreSQL's recursive queries. If it's some kind of homework where you need to actually write those functions (is_parent, is_child) I would also recommend you to implement them using recursive queries.

Adding to Pablo's response...

Using recursive queries, is_parent() is going to be very fast. Assuming you have an index on parent_sector_id, you'll basically do one index scan per depth level.

is_child(), by contrast, is going to be extremely slow if you implement it in a naive manner and have a huge tree, aka don't grab all children and checking for your node.

If you've such a tree and occasionally need to retrieve all children, one good option is to implement a pre-ordered tree algorithm (using floats or numerics; not integers, because they tremendously slow down writes) or nested intervals.

If not, simply reverse the arguments and call is_parent(), i.e. the sql function is_child(a, b) would return is_parent(b, a).

Last but not least, you might want to look into is the ltree datatype in contrib. Using it, you can do is_parent/is_child queries with a gist index on arbitrary nodes.