Reference psql parameter inside PL/pgSQL anonymous block

Question

I'd like to pass a parameter to an anonymous PL/pgSQL block via psql command line, then check that parameter in a conditional.

The relevant part of the SQL is here:

do $$
begin
    if (':para' = 1) then
        -- statements;
    end if;
end $$
;

I call this script as such:

psql -d dbname -v para=1 < script.sql

I receive the error:

ERROR:  invalid input syntax for integer: ":para"
LINE 1: SELECT (':para' = 1)
            ^
QUERY:  SELECT (':para' = 1)
CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF

I tried using the case/when paradigm, which did not work either.

I am guessing that a psql parameter is not compatible with PL/pgSQL? Ultimately, my goal is to run a single delete statement if I pass 1 as a psql parameter, and not run it if I pass 0.

Essentially the same as http://stackoverflow.com/questions/34753914 — Daniel Vérité, Jan 21 '16 at 01:07

Clodoaldo Neto · Accepted Answer · 2016-01-21T12:47:43.790

The psql parser can't see what is inside strings. This might be what you want:

delete from t
where :para = 1

Do it outside of an anonymous block. If you really need PL/pgSQL use a parameterized function:

create or replace function f(_para integer)
returns void as $$
begin
    if _para = 1 then
        --statements
    end if;
end; $$ language plpgsql;

And your script file will have:

select f(:para);

If you do not want to permanently add a function to the db do it all inside the script:

drop function if exists f_iu7YttW(integer);

create or replace function f_iu7YttW(_para integer)
returns void as $$
begin
    if _para = 1 then
        --statements
    end if;
end; $$ language plpgsql;

select f_iu7YttW(:para);

drop function f_iu7YttW(integer);

Give the function an unique name so you do not run the risk of dropping something else.

complementary techinique here: https://stackoverflow.com/a/9981540/48729 — Click Ok, Jul 07 '20 at 23:01

Reference psql parameter inside PL/pgSQL anonymous block

1 Answers1

Linked