The main error are double quotes where you need single quotes for the literal value:
INTERVAL "18 hour"
INTERVAL '18 hour'
But since that's nested in a quoted string I suggest outer dollar quotes:
EXECUTE 'update' || i || $u$SET observation_time = date_added + INTERVAL '18 hour'$u$;
Related:
Better yet, quote the table name properly using format()
:
EXECUTE format ($u$UPDATE %I SET observation_time = date_added + INTERVAL '18 hour'$u$, i);
And you can't use a record
variable like you did. And some more improvements, resulting in:
CREATE OR REPLACE FUNCTION obs_updated_date()
RETURNS int AS
$func$
DECLARE
_tbl regclass;
_ct int := 0;
BEGIN
FOR _tbl IN
SELECT oid
FROM pg_class
WHERE relname LIKE 'traffic%1'
AND relkind = 'r' -- only actual tables
AND relnamespace::regnamespace::text NOT LIKE 'pg_%' -- no system tables
LOOP
EXECUTE format ($u$UPDATE %I SET observation_time = date_added + interval '18 hour'$u$, _tbl);
_ct := _ct + 1;
END LOOP;
RETURN _ct; -- return sth useful: the number of affected tables
END
$func$ LANGUAGE plpgsql;
You don't need a record
to begin with. text
would do it. Better, yet, use type regclass
for the table names, since that automatically adds schema names where required. Else, this might go terribly wrong with table names that are used in multiple schemas.
For 1-time use (as indicated by your dangling DROP FUNCTION
) consider a DO
statement instead of a function:
DO
$do$
DECLARE
_tbl regclass;
_ct int := 0;
BEGIN
...
RAISE NOTICE '% tables updated.', _ct;
END
$do$;