CODEWITHSUNDEEP
databasefunctionpostgresql
David S.
David S.

Reputation: 11168

Column naming in Postgresql functions

I want to create a PSQL function which returns a table like result. My function definition looks like below:

create or replace function myfunc1() 
   returns table(id varchar(50), title varchar(50)) 
as $$ 
begin 
   return query select id, title from books; 
end; 
$$ language plpgsql;

But, when I call the function, I got this error:

ERROR: column reference "id" is ambiguous LINE 1: select id, title from books ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column.

Apparently, it is because I use the column name 'id' in both the return table and my target table. The simplest solution would be changing the column names. But I am here to look for a way that would let me use the column name I want. Besides, changing the name would make my code weird, and difficult for other to understand.

Upvotes: 2

Views: 4105

Answers (3)

Denis de Bernardy
Denis de Bernardy

Reputation: 78423

Remove the ambiguity by qualifying column names:

create or replace function myfunc1()
  returns table(id varchar(50), title varchar(50)) as
$$
  select books.id, books.title from books;
$$ language sql stable;

Upvotes: 3

Milen A. Radev
Milen A. Radev

Reputation: 62573

That ... returns table(id varchar(50), title varchar(50)) ... part of the function definition is just another way to define OUT parameters. So now you have two OUT parameters named id and title and two columns (from the book table) you refer to named... yes, you guessed it - id and title.

A way to avoid this ambiguity is to rename the column names of the returned table in your function definition:

CREATE OR REPLACE FUNCTION myfunc1() RETURNS TABLE(p_id VARCHAR(50), p_title VARCHAR(50)) AS $_$
BEGIN
  RETURN QUERY SELECT id, title FROM books;
END;
$_$ LANGUAGE plpgsql;

Upvotes: 0

Matthew Ratzloff
Matthew Ratzloff

Reputation: 4623

SELECT books.id, title FROM books?

Upvotes: 1

Related Questions