#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12

    finding schema output for added procedures/functions


    ok!

    I have created a bunch of plpgsql stored procedures that run when triggers are fired, and one that I just run from psql.

    What I would like to find out is if there is any way to go in and find these and output them, say for revision control through CVS or for comparison to another database?

    I've read through options on pg_dump and psql and I haven't found any that give me what I want. Any ideas? Thanks!

    ps I do still have the code I used to create them, and they were all made with CREATE OR REPLACE, so they can be easily updated, but I think the benefit is pretty obvious of being able to go in and get this stuff.
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Maybe this will help to get you started, it's the query that phppgadmin (not sure which version) uses when listing functions for a database:

    PHP Code:
    SELECT 
                        pc
    .oid,
                        
    proname
                        
    pt.typname AS result
                        
    oidvectortypes(pc.proargtypes) AS arguments
                    FROM 
                        pg_proc pc
    pg_user pupg_type pt
                    WHERE
                        pc
    .proowner pu.usesysid
                        
    AND pc.prorettype pt.oid
                        
    AND pc.oid '$max'::oid
                    UNION
                    SELECT 
                        pc
    .oid,
                        
    proname
                        
    'OPAQUE' AS result
                        
    oidvectortypes(pc.proargtypes) AS arguments
                    FROM 
                        pg_proc pc
    pg_user pupg_type pt
                    WHERE
                        pc
    .proowner pu.usesysid
                        
    AND pc.prorettype 0
                        
    AND pc.oid '$max'::oid
                    ORDER BY
                        proname
    result 
    Of course you're going to need to fill in what those PHP vars are, but that should at least get you some info on what system tables to look into for that type of info.

    HTH,
    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    this is exactly what I was looking for, thanks. I'm going to dig around in these system tables for a bit, but if you can determine a way to make it spit out the code I put in for a given function, that would also be most helpful!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    nevermind, I got it.

    SELECT * FROM pg_proc WHERE proname = 'user_defined_function';

    that'll spit out the code you wrote for any function

    CVS here we come

IMN logo majestic logo threadwatch logo seochat tools logo