Unable to get value for input parameter inside postgres function-CodePudding

In the below postgres function I am passing 'sample' (integer) as an input parma but when I try to print it's value using raise notice inside the function body I get the following error

ERROR: column "sample" does not exist LINE 1: SELECT sample

CREATE OR REPLACE FUNCTION public.test_function(
        sample integer)
        RETURNS json
        LANGUAGE 'sql'
        COST 100
        VOLATILE PARALLEL UNSAFE
    AS $BODY$
    DO  $$
    BEGIN
      raise notice 'test: %',sample;
    END
    $$;
    select json_agg(1);
    $BODY$;

select "test_function"(10);

CodePudding user response：

It's the anonymous pl/pgsql DO block that does not 'see' the sample parameter. Here is a rewrite in pl/pgsql that does 'see' it.

CREATE OR REPLACE function test_function(sample integer)
RETURNS json LANGUAGE plpgsql COST 100 VOLATILE PARALLEL UNSAFE AS
$BODY$
  BEGIN
    raise notice 'test: %',sample;
    return json_agg(1   sample);
  END
$BODY$;

pl/pgsql DO blocks are more or less encapsulated and can not return anything either.