0

I'm trying to set up an function that analyzes tables on the weekends.

create or replace function public.sp_analyze(t text)
returns void as 
$$
BEGIN

  if to_char(current_Date,'dy') = 'fri' THEN
    --RAISE NOTICE '%', t;
    execute 'vacuum analyze' || t
  end if;
end;
$$ 
LANGUAGE plpgsql SECURITY DEFINER;

Is there a way to execute my analyze command without throwing errors?

This is my error:

ERROR: 42601: syntax error at or near "end"
suhprano
  • 1,723
  • 1
  • 16
  • 22
  • ERROR: 42601: syntax error at or near "end" – suhprano Feb 23 '15 at 20:50
  • Please refer -> https://www.postgresql.org/message-id/C97A7021-C91B-41F3-8902-8CC41F5F5511%40gmail.com Quoting it: You cannot use VACUUM in a function because it's not a transaction-safe operation. – MadhavKhakhar Jul 26 '18 at 07:36

1 Answers1

1

From looking at the code, I suspect the error you're getting is about a non-existing relation.

The code execute 'vacuum analyze' || t is going to append the text that is passed in directly to the string.

Say that t is foo. The resulting string that will try to be executed will be:

execute 'vacuum analyzefoo'

Which will yield an error of this form:

ERROR: relation "analyzefoo" does not exist

which I suspect is the type of error you were seeing.

If a space is added to the constant string, e.g.:

execute 'vacuum analyze ' || t;

this will be avoided.

Edit in response to comment from OP:

If you have a space in the command (which implies there's a space in t), then it's likely the semicolon missing on the execute statement leading to the syntax error. I've added that in my code snippet above.

khampson
  • 14,700
  • 4
  • 41
  • 43