[Chicago-talk] Suppressing "NOTICE:" in postgres

Don Drake don at drakeconsult.com
Mon Mar 23 20:11:23 PDT 2009

For a) and b), I would try playing around with $dbh->err() and $dbh- 
 >errstr() to get the NOTICE message, and to disable it, try setting  
PrintError to false in your connection string.

For c), I've had to do that sort of thing before (as well as analyze  
indexes for table partitioning), so here's the code to do that.  Just  
change the c.relname=lower('your_table_name') and the lower(n.nspname)  
is the schema.

   SELECT i.conname, pg_catalog.pg_get_constraintdef(i.oid) as ddl
        FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,  
pg_catalog.pg_constraint i
        WHERE c.oid = (SELECT  distinct c.oid
               FROM pg_class c INNER JOIN pg_namespace n ON  
c.relnamespace = n.oid
               WHERE c.relkind = 'r'
               AND c.relname = lower('emails')
               AND lower(n.nspname) = lower('spamdb')
        AND c.oid = i.conrelid AND i.conrelid = c2.oid
        AND i.contype !='c'
        ORDER BY i.contype desc;

Hope that helps.


Don Drake

On Mar 23, 2009, at 9:36 PM, Michael Potter wrote:

> Chicago Perl Crew,
> When I do this :
>   $sql = qq{ DROP TABLE "terminalXgroup" CASCADE };
>   $dbh->do($sql);
> I get this message:
> NOTICE:  drop cascades to constraint terminalXgroup_group_fkey on
> table "terminalXgroup"
> a) What are the ways to suppress this message?
> b) Is there a convenient way to capture this message in a variable
> rather than letting it go to the terminal?
> c) Is there a way to drop all constraints on a table without knowing
> their names?
> for c) I could drop the constraints before I drop the table; that way
> the notice message would not be active.  I know how to drop a single
> constraint by name, but I can not find how to get the list of
> constraints in postgres.
> My goal is the drop a set of tables and not have a bunch of messages
> come out that I do not care about.  Even if I do a) or b) I think I
> still want to do c).
> I am not willing to name all my constraints; I think I would rather
> live with the notice messages.
> -- 
> Michael Potter
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20090323/719aa1f3/attachment.html>

More information about the Chicago-talk mailing list