1

I have an error while restoring DB from dump. What does it mean?

ERROR:  syntax error at or near "FUNCTION"
LINE 1: ...LETE ON public.currency_rate FOR EACH ROW EXECUTE FUNCTION p...


--
-- Name: currency_rate currency_rate_bt_delete; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER currency_rate_bt_delete 
INSTEAD OF DELETE ON public.currency_rate 
FOR EACH ROW 
EXECUTE FUNCTION public.currency_rate_bt_delete();
Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
muhana
  • 101
  • 1
  • 8

2 Answers2

1

The problem of your dump/restore is that your create dump with PostgreSQL v13 It generates dump you have shown.

But then you try to restore this dump on PostgreSQL v10 which does not understand that dump

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
0

You have to use PROCEDURE instead of FUNCTION before public.currency_rate_bt_delete() Your trigger query should be like below:

CREATE TRIGGER currency_rate_bt_delete 
INSTEAD OF DELETE ON public.currency_rate 
FOR EACH ROW 
EXECUTE PROCEDURE public.currency_rate_bt_delete();

NOTE : - This answer is limited to the error mentioned in the question.

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
  • The query I have show is produced automatically by `pg_dump`. So if it was dumped by pg tool why it is not restored by same tools? – muhana Oct 14 '20 at 08:21
  • This sql is exactly from dump. I do not do that manually – muhana Oct 14 '20 at 13:15