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.kla...@aklaver.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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.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.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >