Passing parameter to SQL Function in plpgsql

Question

I have written the below function and it works in Postgres 12.5:

CREATE OR REPLACE FUNCTION export_to_s3()
        RETURNS TRIGGER
AS $export_to_s3$
BEGIN
        PERFORM aws_s3.query_export_to_s3(
                'select * from test.allowed_object_status',
                aws_commons.create_s3_uri(
                        'test-db-dumps',
                        'allowed_object_status.csv',
                        'eu-west-2'),
                options :='format csv, delimiter $$,$$'
        );
        RETURN NEW;
END;
$export_to_s3$ LANGUAGE plpgsql;

Now i would like to make test.allowed_object_status and allowed_object_status.csv parameters that i pass into export_to_s3().

I have tried a few things but failed so any help would be great.
A second part to my question and less important is it possible for a function to return nothing/void. I made this function a trigger but thats only because I cant figure out how to declare a function that dose not return anything after executing

Solution
This the solution i come up with thanks to the comments from @404 below.

CREATE OR REPLACE FUNCTION export_to_s3(query_cmd text, bucket_prefix text)
RETURNS VOID
AS $export_to_s3$
BEGIN
PERFORM
    aws_s3.query_export_to_s3(
        query_cmd,
        aws_commons.create_s3_uri(
            'test-db-dumps',
            bucket_prefix,
            'eu-west-2'),
        options :='format csv, delimiter $$,$$'
    );
END;
$export_to_s3$ LANGUAGE plpgsql;

I am sure there is a better way of doing it but very new to plpgsql

Just to be clear, are you actually looking to make a trigger function or just a regular function? If it needs to be a trigger then maybe you can get those values in there by setting environment variables somewhere which the trigger function reads... if it doesn't need to be a trigger function then it's much easier, just a standard function with params. "is it possible to a function to return nothing" - "a function", yes of course, define it as RETURNS VOID; but it's not clear if it needs to be a trigger function or not. — 404
hey @404, apologies for the bad writing and confusion. Yes it should be a function that return nothing so i will use what you suggested RETURNS VOID. How would i pass parameter to it ? — alexis
Ok if it's just a regular function then see postgresql.org/docs/current/sql-createfunction.html for how to use params etc. A trigger function is a bit different, and a regular function can't be used as a trigger. — 404
@404 this was enough for me to come up a solution ... if you like to provide it as an answer would be happy to accept it as such. Thanks — alexis

Laurenz Albe Laurenz Albe · Accepted Answer · 2021-03-08T10:31:57

You can pass arguments to a trigger function, but that works different than you'd imagine.

You declare the trigger function without parameters.
In the CREATE TRIGGER statement, you specify string literals as function arguments.
These parameters can be accessed in the PL/pgSQL function body through TG_ARGV[], and the argument count is in TG_NARGS.

See the documentation for details.

Passing parameter to SQL Function in plpgsql

1 Answers