Syntax error at or near "(" while calling function

Question

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();

You are missing spaces in the update clause, use: `EXECUTE 'update ' || i || ' SET ...` — Tim Biegeleisen, Aug 05 '19 at 04:58
Yes same error: ERROR: syntax error at or near "("; error while executing the query(7) — Amiclone, Aug 05 '19 at 05:05
sorry for that. The error message is edit one. To confirm the error is ERROR: syntax error at or near "("; error while executing the query(7) — Amiclone, Aug 05 '19 at 05:13
I get a different error: `ERROR: syntax error at or near "("` `LINE 1: update(traffic_a_1)SET observation_time = date_added + INTER...` — melpomene, Aug 05 '19 at 05:16
`SELECT r FROM pg_class` is an invalid query. `pg_class` has not column named `r` — , Aug 14 '19 at 07:26

Erwin Brandstetter · Answer 1 · 2019-08-05T05:51:20.497

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

Insert text with single quotes in PostgreSQL

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.

Table name as a PostgreSQL function parameter

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

You also cannot use a `record` variable like you did. – Erwin Brandstetter Aug 05 '19 at 05:24 — Erwin Brandstetter, Aug 05 '19 at 05:24

melpomene · Answer 2 · 2019-08-05T05:31:29.240

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;

Syntax error at or near "(" while calling function

2 Answers2