Re: RETURNS TABLE function: ERROR: column reference "word" is ambiguous

need to return query with alias

*example:*

create table emp (id integer, ename text);
insert into emp values(1, 'aaa');

create or replace function f_sample1() returns table (id integer, ename
text) as $$
declare
begin
return query select id, ename from emp;
end$$ language plpgsql;

select f_sample1(); ---- this will throw ERROR: column reference "id"
is ambiguous LINE 1: select id, ename from emp

create or replace function f_sample1() returns table (id integer, ename
text) as $$
declare
begin
return query select a.id, a.ename from emp a;
end$$ language plpgsql;

select f_sample1(); ---- success

thanks
Sridhar

On Thu, Aug 11, 2016 at 1:56 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 08/10/2016 01:14 PM, Alexander Farber wrote:
>
>> No, actually both variants work for me right now at 9.5.3 on Mac -
>>
>
> I thought the question you where asking was:
>
> "Where does RETURN NEXT EXPRESSION work, on 9.6?"
>
> In the examples below you are not doing that.
>
> Inline comments below.
>
>
>> On Wed, Aug 10, 2016 at 8:31 PM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>>
>> Given what you are doing, RETURN TABLE it will not work there for
>> the same reason it does not work in 9.5:
>>
>> https://www.postgresql.org/docs/9.6/static/plpgsql-control-
>> structures.html#PLPGSQL-STATEMENTS-RETURNING
>> <https://www.postgresql.org/docs/9.6/static/plpgsql-control-
>> structures.html#PLPGSQL-STATEMENTS-RETURNING>
>>
>> "If you declared the function with output parameters, write just
>> RETURN NEXT with no expression. On each execution, the current
>> values of the output parameter variable(s) will be saved for
>> eventual return as a row of the result. Note that you must declare
>> the function as returning SETOF record when there are multiple
>> output parameters, or SETOF sometype when there is just one output
>> parameter of type sometype, in order to create a set-returning
>> function with output parameters."
>>
>>
>> Either:
>>
>> CREATE OR REPLACE FUNCTION words_check_words(
>> IN in_uid integer,
>> IN in_gid integer,
>> IN in_tiles jsonb
>> OUT out_word varchar,
>> OUT out_score integer
>> ) RETURNS SETOF RECORD AS
>> $func$
>>
>>
>> Or:
>>
>> CREATE OR REPLACE FUNCTION words_check_words(
>> IN in_uid integer,
>> IN in_gid integer,
>> IN in_tiles jsonb
>> ) RETURNS TABLE (out_word varchar, out_score integer) AS
>> $func$
>>
>>
>> And then I assign values to the variables and call RETURN NEXT:
>>
>> out_word := ... ;
>> out_score := ... ;
>> RETURN NEXT;
>>
>
> RETURN SETOF and RETURN TABLE are comparable, as you found out:
>
> https://www.postgresql.org/docs/9.5/static/plpgsql-overview.
> html#PLPGSQL-ARGS-RESULTS
>
> "PL/pgSQL functions can also be declared with output parameters in place
> of an explicit specification of the return type. This does not add any
> fundamental capability to the language, but it is often convenient,
> especially for returning multiple values. The RETURNS TABLE notation can
> also be used in place of RETURNS SETOF."
>
>
>
>> Regards
>> Alex
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>