Understanding PostgreSQL select operator

Question

I'm writing the following stored procedure:

CREATE OR REPLACE FUNCTION getid() RETURNS table(id integer) AS $$
DECLARE
rec RECORD;
BEGIN
    select id into rec from player where id = 3;
END $$
LANGUAGE plpgsql;

select * from getid();

And when I'm trying to execute that script I got the error:

column reference "id" is ambiguous

Why? I thought that id column of the returned table is not participate in the select operator...

The issue is that it worked on PostgreSQL 8.4 but doesn't work on PosgtreSQL 9.4. Couldn't you explain what actually has been added in the PostgreSQL 9.4 so it doesn't work?

percy · Accepted Answer · 2015-04-07 13:05:39Z

3

Postgres is confused when you use the same names for arguments and columns... Consider using a convention with some prefix for all input parameters - for example p_id I would write:

CREATE OR REPLACE FUNCTION getid() RETURNS table(p_id integer) AS $$
DECLARE
rec RECORD;
BEGIN
    SELECT INTO rec id FROM player WHERE id = 3;
END $$
LANGUAGE plpgsql;

answered Apr 7, 2015 at 13:05

percy

1,12811 silver badges9 bronze badges

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

Comments

Joshua Tyree · Accepted Answer · 2015-04-07 13:06:31Z

2

To solve that specific problem you would do this in your select:

SELECT player.id INTO rec FROM player WHERE player.id = 3;

answered Apr 7, 2015 at 13:06

Joshua Tyree

731 silver badge5 bronze badges

Collectives™ on Stack Overflow

Understanding PostgreSQL select operator

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related