Re: stack depth limit exceeded

If you are working with Postgres version >= 8.4, you should look at the WITH
RECURSIVE (called recursive CTEs) feature:

http://www.postgresql.org/docs/8.4/static/queries-with.html

Regards,

On Thu, Mar 31, 2011 at 12:19 PM, salah jubeh <s_jubeh(at)yahoo(dot)com> wrote:

>
>
> 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
>
>

--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company