Обсуждение: function returning a merge of the same query executed X time

Hi all,
I'm having difficulties to  create a function that should execute X time the same query and return  their results as a
singletable. 
I tried this way but it don't work :
Thanks to help.

create function cm_get_json_loop_info(
    IN profile integer,
    IN distance double precision DEFAULT 10,
    IN x_start double precision DEFAULT 0,
    IN y_start double precision DEFAULT 0,
    IN nbr integer default 1)

    returns setof json as
    $BODY$
    declare jsona json;
    BEGIN
    for json a i in 1.. nbr loop

    select row_to_json(q)
    from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as
total_length,json_agg(row_to_json(r))as data  
        from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q
     return next jsona
    end loop;
    return jsona;

Marc

On 4/22/15 8:14 AM, Marc-André Goderre wrote:
>      select row_to_json(q)
>     from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as
total_length,json_agg(row_to_json(r))as data 
>         from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q

Untested...

CREATE FUNCTION ...(
   ...
   , iterations int
)
LANGUAGE sql AS
$body$
     select row_to_json(q)
    from (select row_number() over() as id, sum(cost) as
total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data
        from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q
        , generate_series(1, iterations) i
$body$
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 04/22/2015 06:14 AM, Marc-André Goderre wrote:
> Hi all,
> I'm having difficulties to  create a function that should execute X time the same query and return  their results as
asingle table. 
> I tried this way but it don't work :
> Thanks to help.

See comments inline.
>
> create function cm_get_json_loop_info(
>      IN profile integer,
>      IN distance double precision DEFAULT 10,
>      IN x_start double precision DEFAULT 0,
>      IN y_start double precision DEFAULT 0,
>      IN nbr integer default 1)
>
>      returns setof json as
>      $BODY$
>      declare jsona json;
>      BEGIN
>      for json a i in 1.. nbr loop
            ^^^^^^^
            Not your declared variable. Besides which if the variable
where correct you would just be assigning it the numbers 1 through nbr.

See:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS

40.6.3.5. FOR (Integer Variant)

>
>      select row_to_json(q)
>     from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as
total_length,json_agg(row_to_json(r))as data 
>         from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q
>       return next jsona

The output of the query is not assigned to jsona.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT

40.5.3. Executing a Query with a Single-row Result

>      end loop;
>      return jsona;

This should be just RETURN.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

40.6.1.2. RETURN NEXT and RETURN QUERY
>
> Marc
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks Geoff for your idea but my query return something different each time I call it. This way, 

select row_number()over() as id,q.* 
    from (select sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data 
        from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q CROSS JOIN generate_series(1, 3)

the quey is executed only 1 time.

Thanks

Marc

De : gwinkless@gmail.com [mailto:gwinkless@gmail.com] De la part de Geoff Winkless
Envoyé : 22 avril 2015 11:22
À : Marc-André Goderre
Cc : 'pgsql-general@postgresql.org' (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] function returning a merge of the same query executed X time

Can you not just CROSS JOIN it to generate_series(1, 8)?

On 22 April 2015 at 14:14, Marc-André Goderre <magoderre@cgq.qc.ca> wrote:
Hi all,
I'm having difficulties to  create a function that should execute X time the same query and return  their results as a
singletable.
 
I tried this way but it don't work :
Thanks to help.

create function cm_get_json_loop_info(
    IN profile integer,
    IN distance double precision DEFAULT 10,
    IN x_start double precision DEFAULT 0,
    IN y_start double precision DEFAULT 0,
    IN nbr integer default 1)

    returns setof json as
    $BODY$
    declare jsona json;
    BEGIN
    for json a i in 1.. nbr loop

    select row_to_json(q)
        from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as
total_length,json_agg(row_to_json(r))as data
 
                from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q
        return next jsona
    end loop;
    return jsona;

Marc



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general