Обсуждение: stack depth limit exceeded

 

Hello,

I have written this function which is simply returns the entities which depends on a certain entity. It works fine if the dependency tree is not long. However, If I have an entity which are linked to many other entities I get

stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth", after ensuring the platform's stack depth limit is adequate.

I wrote this function to know exactly what are the tables, views that will be doped if I use cascade option.  I want to get around this issue without changing the server configuration


CREATE OR REPLACE FUNCTION dependon(var text)
  RETURNS SETOF text AS
$BODY$
    DECLARE
        node record;
        child_node record;
    BEGIN
   
        FOR node IN -- For inheritance
        SELECT objid::regclass::text as relname   
        FROM pg_catalog.pg_depend
        WHERE refobjid = $1::regclass::oid AND deptype ='n' AND classid ='pg_class'::regclass
        UNION
        -- For rewrite rules
        SELECT ev_class::regclass::text as relname
        FROM pg_rewrite WHERE oid IN ( SELECT objid FROM pg_catalog.pg_depend
        WHERE refobjid = $1::regclass::oid AND deptype ='n')
        UNION
        -- For constraints (Forign keys)
        SELECT conrelid::regclass::text as relname
        FROM pg_constraint WHERE oid in (SELECT objid FROM pg_catalog.pg_depend
        WHERE refobjid = $1::regclass::oid AND deptype ='n')

        LOOP    

            RETURN NEXT node.relname;
            FOR child_node IN SELECT * FROM dependon(node.relname)
                LOOP
            RETURN NEXT child_node.dependon;
                END LOOP;
            
        END LOOP;
    END
    $BODY$
  LANGUAGE 'plpgsql'

Regards