CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1postgresql-9.5
Amiclone
Amiclone

Reputation: 408

Syntax error at or near "(" while calling function

While running this code, I am getting following error:

ERROR: syntax error at or near "(";
error while executing the query(7)

Code:

CREATE OR REPLACE FUNCTION obs_updated_date() RETURNS INTEGER AS $$
DECLARE
  i RECORD;
BEGIN
  FOR i IN
    (SELECT r FROM pg_class
      WHERE r LIKE 'abc%1')
  LOOP
    EXECUTE 'update' || i || 'SET observation_time = xyz + INTERVAL"18 hour"';
  END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT obs_updated_date();
DROP FUNCTION obs_updated_date();

Upvotes: 0

Views: 2331

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656962

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$;

Upvotes: 2

melpomene
melpomene

Reputation: 85767

There are three errors in the code:

  • i is a RECORD, i.e. a whole row of results. Concatenating it to a string implicitly puts ( ) around it, producing update(trafficfoo1)SET .... This is what causes the syntax error. You need to either use i.relname or declare i as TEXT.

  • There are missing spaces around i in the generated string.

  • The wrong kind of quotes are used for "18 hour". Double quotes are for identifiers, not strings. '18 hour' (single quotes) work, but in the context of a single-quoted string ' needs to be escaped as '', giving ''18 hour''.

A working version:

CREATE OR REPLACE FUNCTION obs_updated_date() RETURNS INTEGER AS $$
DECLARE
  i TEXT;
--  ^^^^
BEGIN
  FOR i IN
    SELECT relname FROM pg_class
      WHERE relname LIKE 'traffic%1'
  LOOP
    execute 'update ' || i || ' SET observation_time = date_added + INTERVAL''18 hour''';
--                 ^           ^                                            ^^       ^^
  END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions