Postgresql does not detect duplicate name when declaring a parameter or variable with the same name in a function

Question

I use the PG 13.1 version, I do not know if this is a bug or is the default behavior, have the following example

DROP FUNCTION IF EXISTS test;
CREATE OR REPLACE FUNCTION test(
    v1 int,
    v2 float
) RETURNS boolean AS
$$
DECLARE
    v1 int;
    v2 float;
BEGIN
    RAISE INFO 'v1 => %, v2 => %', v1, v2;
    RETURN true;
END;
$$
LANGUAGE plpgsql;

SELECT test(10,20);

Showing

INFO:  v1 => <NULL>, v2 => <NULL>

Successfully run. Total query runtime: 79 msec.
1 rows affected.

Could someone please explain why Postgres does not detect the "duplicate name" error?

Obviously the result it shows is that of the variables of the DECLARE section

Yup. That is how scoping rules work. I like to prefix the parameters going in with in_, out_, or perhaps p_. — Gordon Linoff
– Gordon Linoff, Commented Dec 18, 2020 at 17:04

Belayer · Accepted Answer · 2020-12-22 04:26:50Z

1

As indicated by @Gordon you are running across Postgres's scooping rules. Variables created as parameters have (for lack of a better term) Functional scope while those within the block have (again for lack of a better term) Block scope. When references without qualification, the reference is always to the most resent definition, even within nested blocks. Try the following; the values assigned are irreverent just notice results and where variable defined:

create or replace 
function test(
               v1 int 
             , v2 float
             )
 returns boolean  
language plpgsql 
as $$
declare                 -- an outer block 
    v1 int = 18;
    v2 float = 45.55;
begin
    declare            -- a nested inner block
        v1 text := 'Same name, different type';
        v2 date := now()::date; 
    begin 
        raise info 'Inner Block: v1 => %, v2 => %', v1, v2; 
    end; 

    raise info 'Outer Block: v1 => %, v2 => %', v1, v2;
    raise info 'Function Parameters: v1 => %, v2 => %', test.v1, test.v2; -- Qualified    
    return true;
end;
$$;

edited Dec 22, 2020 at 4:26

answered Dec 19, 2020 at 21:07

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

1 Comment

Panchove Over a year ago

Excellent explanation, very clear, thank you very much!

Collectives™ on Stack Overflow

Postgresql does not detect duplicate name when declaring a parameter or variable with the same name in a function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related