[KLUG Members] Postgres SQL query

[Andrew Eidson]

Ok I have this query

SELECT 'UPDATE ' || t.relname || ' SET ' || c.attname || ' = ' || c.attname
|| ' + INTERVAL ''1 hour'''
FROM pg_class t, pg_attribute c, pg_type ty
WHERE c.attrelid=t.oid
  AND c.atttypid=ty.oid
  AND ty.typname='date'
  AND t.reltype > 0
ORDER BY t.relname, c.attname

Which does a nice job of creating the 163 update queries that need to be
run.. Is there a quick way of automating this in psql or PGAdmin? Or would I
need to pipe this out to a file and then run some type of script to the file
to get it to run all the update queries? The other issue I can see is when I
write it to a file in PGAdmin it does not have the proper syntax so I would
have to edit the file it may be easier to run the 163 queries individually
but I know this will happen again in the future so trying to figure out a
way of automating this. 
 

-----Original Message-----
From: members-bounces at kalamazoolinux.org
[mailto:members-bounces at kalamazoolinux.org] On Behalf Of Adam Tauno Williams
Sent: Tuesday, November 15, 2005 2:13 PM
To: The main KLUG mailing list.
Subject: Re: [KLUG Members] Postgres SQL query

> I have a Postgres sql database with well over 200 tables. Almost every 
> table has a datetime field in it. When I pull the data from the old MS 
> SQL database and import it through a utility that was created for me 
> it shifts the dates back 1 hour which causes all sorts of havoc.. The 
> people who developed the utility think it is a jdbc issue but have no 
> idea how to correct it. Is there a query that can find all the 
> datetime type fields in a database and then advance that field 1 hour? 
> I can do it table by table but was wondering if someone has more 
> elegant way to do everything at once in their memory banks. If not I 
> guess I will be working late the next coupld of days to get this fixed..
Otherwise thanks in advance.

You can easily enough identify all the timestamp fields:
SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type LIKE ('timestamp%')

Doing a loop for each row in a PL/SQL procedure should be simple enough.
http://www.postgresql.org/docs/8.1/interactive/plpgsql.html