CODEWITHSUNDEEP
sqlpostgresql
St.Antario
St.Antario

Reputation: 27375

Understanding PostgreSQL select operator

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?

Upvotes: 1

Views: 69

Answers (2)

Joshua Tyree
Joshua Tyree

Reputation: 73

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

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

Upvotes: 2

percy
percy

Reputation: 1118

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;

Upvotes: 3

Related Questions