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

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Wed, 10 Aug 2016 10:42:56 -0700

On 08/10/2016 10:19 AM, Pavel Stehule wrote:

2016-08-10 19:05 GMT+02:00 Alexander Farber <alexander.farber@xxxxxxxxx
<mailto:alexander.farber@xxxxxxxxx>>:

    Thank you Adrian and others -

    I am trying to replace INSERT into temp table in my custom function
    by RETURN NEXT, but get an error:

        CREATE OR REPLACE FUNCTION words_check_words(
                IN in_uid integer,
                IN in_gid integer,
                IN in_tiles jsonb)
                RETURNS TABLE(word varchar, score integer) AS
        $func$
        .......

                            -- INSERT INTO _words(word, score)
                            -- VALUES (upper(_word), _score);

                            RETURN NEXT (word, score);

    ERROR:  RETURN NEXT cannot have a parameter in function with OUT
    parameters
    LINE 98:                         RETURN NEXT (word, score);

This was limit in older version

you have to assign values to these variables and call RETURN NEXT
without any parameters

CREATE OR REPLACE FUNCTION public.foob(OUT a integer, OUT b integer)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
BEGIN
  a := 10; b := 20;
  RETURN NEXT;
  b := 30;
  RETURN NEXT;
END;
$function$

result
┌────┬────┐
│ a  │ b  │
╞════╪════╡
│ 10 │ 20 │
│ 10 │ 30 │
└────┴────┘
(2 rows)

To build on this:

CREATE OR REPLACE FUNCTION public.foob(a integer,  b integer)
 RETURNS TABLE(c integer, d integer)
 LANGUAGE plpgsql
AS $function$
BEGIN
    FOR i in 1..10 LOOP
        c := a + i;
        d := b + i;
      RETURN NEXT;
    END LOOP;
END;
$function$
;

aklaver@test=> select * from foob(1, 2);
 c  | d
----+----
  2 |  3
  3 |  4
  4 |  5
  5 |  6
  6 |  7
  7 |  8
  8 |  9
  9 | 10
 10 | 11
 11 | 12
(10 rows)

    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