On 08/10/2016 10:19 AM, Pavel Stehule wrote:
2016-08-10 19:05 GMT+02:00 Alexander Farber <[email protected]
<mailto:[email protected]>>:
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
[email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
