Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    37

    Unanswered: Loop through every Table in Schema

    Hi guys
    I am trying to write a function that will loop through every table in my schema and if a specific column exists, will set the default value. I need a few ideas as to how this would be done using plpgsql? Heres a sort of example of what I'm trying to do.

    Code:
    CREATE OR REPLACE FUNCTION updateDateCreated()
    RETURNS VOID
    AS
    $$
    BEGIN
    
    FOR t table IN public
    
    LOOP
    
    IF EXISTS t.datecreated
    	ALTER TABLE t
    	ALTER COLUMN t.datecreated
    	{SET DEFAULT now()}
    
    END IF;
    
    END LOOP;		
    
    END;
    $$Language 'plpgsql';

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Easiest way would be to just run the following query and paste results back into query window and rerun.

    Code:
    SELECT 'ALTER TABLE ' || c.table_schema || '.' || c.table_name ||
      ' ALTER COLUMN datecreated {SET DEFAULT now()};' 
    FROM information_schema.columns c
    WHERE table_schema = 'public'
      AND column_name = 'datecreated'
    If you need to make it self contained, then you'll need to use EXECUTE IMMEDIATE.
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Dynamic SQL
    Last edited by artacus72; 12-23-09 at 12:18.

  3. #3
    Join Date
    Oct 2009
    Posts
    37
    Thanks for this artacus.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •