Unable to get value for input parameter inside postgres function

Question

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);

Stefanov.sm · Accepted Answer · 2021-11-03 15:03:04Z

3

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.

edited Nov 3, 2021 at 15:03

answered Nov 3, 2021 at 14:57

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to get value for input parameter inside postgres function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related