2

The answer to a decade-old StackOverflow question was helpful for me. However, I was unable to write an equivalent function and trigger in "standard" SQL, i.e. where the language parameter specifies sql instead of plpgsql. The hangup appears to be that functions written in standard SQL can't return triggers, though this appears to be required for functions called by triggers in PostgreSQL:

The trigger function must be declared as a function taking no arguments and returning type trigger.

So attempting...

create function func_dummy()
returns trigger as
'update tbl_dummy
set update_date = current_timestamp;'
language sql;

results in:

ERROR:  SQL functions cannot return type trigger
SQL state: 42P13

How can the PostgreSQL-specific function and trigger in the original SO answer be rewritten using "standard" SQL?

Geolographist
  • 25
  • 6

1 Answers1

3

How can the PostgreSQL-specific function [...] be rewritten using "standard" SQL?

That's not possible. The manual:

Trigger functions can be written in most of the available procedural languages [...]

It is also possible to write a trigger function in C, although most people find it easier to use one of the procedural languages. It is not currently possible to write a trigger function in the plain SQL function language.

Bold emphasis mine.

I have written thousands of trigger functions. Almost all of them with LANGUAGE plpgsql.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    You can write a function with language SQL, but it **cannot** be a trigger function. A trigger function must **return ...** either Null or a tuple matching the row from the table. An language SQL function cannot issue return statement. – Belayer Apr 15 '23 at 01:23