CODEWITHSUNDEEP
sqlpostgresql
Michael Hewson
Michael Hewson

Reputation: 1534

postgresql does not recognize variable name introduced in WITH clause

I'm getting an error in my user-defined-function, and to an extent, I understand the error (unrecognized column), but not why it's being caused. Here is the minimal code to reproduce the error:

create table foo (id serial primary key, name varchar not null);

create table bar (id serial primary key, name varchar not null);

create function foo_to_bar(foo_id int) returns void as $$
begin
    with _name as (
        select name from foo where id = foo_id
    )
    insert into bar (name) values (_name);
                    -- error here: ~~~~~
end;

$$ language plpgsql;

insert into foo (name) values ('slimshady');
-- id of first entry will be 1

select foo_to_bar(1);

Error:

ERROR:  column "_name" does not exist
LINE 4:     insert into bar (name) values (_name)
                                           ^
HINT:  Perhaps you meant to reference the column "bar.name".
QUERY:  with _name as (
        select name from foo where id = foo_id
    )
    insert into bar (name) values (_name)

Why is it totally ignoring the _name that I created in the with clause, three line above where the error is being raised? How do I fix this?

Upvotes: 0

Views: 262

Answers (1)

mustaccio
mustaccio

Reputation: 19001

_name is not a variable, it's a derived table name, so your statement should look like

with _name as (
    select name from foo where id = foo_id
)
insert into bar (name) select name from _name;

Upvotes: 2

Related Questions