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