Probably simplest with regclass as input and an OUT parameter:
CREATE OR REPLACE FUNCTION f_last_update(_tbl regclass, OUT _last_update timestamp)
LANGUAGE plpgsql AS
EXECUTE 'SELECT max(updated_at) FROM ' || _tbl
Optionally schema-qualified and double-quoted where ...
string_to_array() in your solution is only useful if you want to allow non-standard array syntax. Else it's simpler and cheaper to just cast. And you can do the assignment at declaration time. Like:
CREATE OR REPLACE FUNCTION example_function()
LANGUAGE plpgsql AS
special_names text := TG_ARGV::text;
For anyone else who comes across this question, I wound up encoding my arrays as strings and then using string_to_array() to decode inside the trigger function.
CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER AS
special_names := string_to_array(TG_ARGV, ', ');
You would have to use the pure string representation of the array.
CREATE TRIGGER on_create_flag_special
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE PROCEDURE example_function(
That should be simple:
ALTER PROCEDURE myproc
SET default_transaction_isolation = 'repeatable read';
The downside to that is that because of the limitations of transaction management inside procedures, you can no longer COMMIT or ROLLBACK inside such a procedure.
If you need that, you could start the procedure body like this:
COMMIT; -- otherwise you ...
When I try to get the data back from the table I get an error.
That's because you cannot return anything from a DO statement. You would need a function where you define the return type in the RETURNS clause. (But you still wouldn't need a temporary table.)
Plus, PL/pgSQL does not allow SELECT without a target.
But you don't need any of this for the example. ...
you need to dynamic sql as seen below
as seen in the last query, you need to use the result of the query and also address the return values
CREATE TABLE towns ("id" int)
INSERT INTO towns VALUEs (1),(2),(3)
select format('%s - %s',min(id),max(id) )min_max_vals from towns;
| min_max_vals |
| :----------- |
| 1 - 3 |
create function ...