CODEWITHSUNDEEP
postgresqlplpgsql
Shaul Behr
Shaul Behr

Reputation: 38003

Why is this PL/PgSql invalid?

N00bie PL/PgSql question:

create or replace function foo() returns int as $$
declare fff int;
begin
    declare fff int;
    select count(*) into fff from mytable;
    return fff;
end 
$$ language plpgsql

output:

ERROR:  syntax error at or near "*"
LINE 5:   select count(*) into fff from mytable;
                 ^
CONTEXT:  invalid type name "count(*) into fff from mytable"

What am I doing wrong?

Upvotes: 0

Views: 1546

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You need to remove declare from begin/end block:

create or replace function foo() returns int as $$
declare fff int;
begin
    --   declare fff int;
    select count(*) into fff from mytable;
    return fff;
end 
$$ language plpgsql

db<>fiddle

Ah. Right. So can you not declare a variable inside the BEGIN...END block?

You declare variables at declare part. If you need you could nest blocks:

create or replace function foo() returns int as $$
declare fff int;
begin
    declare xxx INT;
    begin
    select count(*) into xxx from mytable;
    return xxx;
    end;
end 
$$ language plpgsql

Upvotes: 2

Related Questions