New answers tagged

1

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 $func$ BEGIN EXECUTE 'SELECT max(updated_at) FROM ' || _tbl INTO _last_update; END $func$; Call: SELECT f_last_update('mytable'); Optionally schema-qualified and double-quoted where ...


1

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() RETURNS TRIGGER LANGUAGE plpgsql AS $func$ DECLARE special_names text[] := TG_ARGV[0]::text[]; ...


0

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 $$ DECLARE special_names text[]; special_users text[]; BEGIN special_names := string_to_array(TG_ARGV[0], ', '); ...


2

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( '{magicname1,magicname2}', '{user1,user2}' );


1

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 ...


2

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. ...


0

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 ...


Top 50 recent answers are included