Обсуждение: Recursive function that receives a list of IDs and returns all child IDs

hello pgsql fans out there,

i've already created a function that returns a list of IDs of all sub-samples based on a given sample ID. this works
fine.now i like to extend this function so that it can receive a list of sample IDs. e.g.: 

fn_get_subsamples(IN sample_numbers SETOF integer)


here is the existing function:

CREATE OR REPLACE FUNCTION fn_get_subsamples(IN sample_number integer)
  RETURNS SETOF integer AS
$BODY$
    WITH RECURSIVE recursetree(sample_number) AS (
      SELECT sample_number
      FROM sample
      WHERE parent_sample = $1

      UNION ALL

      SELECT t.sample_number
      FROM sample t
      JOIN recursetree rt ON rt.sample_number = t.parent_sample
    )

    SELECT sample_number
    FROM recursetree;
$BODY$
  LANGUAGE sql VOLATILE



thanks a lot for every hint!
sven
--
GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
gratis Handy-Flat! http://portal.gmx.net/de/go/dsl

"Sven Haag" <sven-haag@gmx.de> writes:
> hello pgsql fans out there,
> i've already created a function that returns a list of IDs of all sub-samples based on a given sample ID. this works
fine.now i like to extend this function so that it can receive a list of sample IDs. e.g.: 

> fn_get_subsamples(IN sample_numbers SETOF integer)

There's no such animal as a function that accepts a set.  You could pass
it an array of integers instead.  Or maybe just call it more than once.

            regards, tom lane

i guess an array is also ok. must been something like:
fn_get_subsamples(IN sample_numbers[] integer) ??

how would a query then look like?

SELECT * FROM sample WHERE sample_number IN ( fn_get_subsamples(1,2,3,4)
) ??

cheers sven




Am 23.03.2011 17:42, schrieb Tom Lane:
> "Sven Haag"<sven-haag@gmx.de>  writes:
>> hello pgsql fans out there,
>> i've already created a function that returns a list of IDs of all sub-samples based on a given sample ID. this works
fine.now i like to extend this function so that it can receive a list of sample IDs. e.g.: 
>> fn_get_subsamples(IN sample_numbers SETOF integer)
> There's no such animal as a function that accepts a set.  You could pass
> it an array of integers instead.  Or maybe just call it more than once.
>
>             regards, tom lane
>

On Wed, Mar 23, 2011 at 10:29 AM, Sven Haag <sven-haag@gmx.de> wrote:
> hello pgsql fans out there,
>
> i've already created a function that returns a list of IDs of all sub-samples based on a given sample ID. this works
fine.now i like to extend this function so that it can receive a list of sample IDs. e.g.: 
>
> fn_get_subsamples(IN sample_numbers SETOF integer)
>
>
> here is the existing function:
>
> CREATE OR REPLACE FUNCTION fn_get_subsamples(IN sample_number integer)
>  RETURNS SETOF integer AS
> $BODY$
>        WITH RECURSIVE recursetree(sample_number) AS (
>          SELECT sample_number
>          FROM sample
>          WHERE parent_sample = $1
>
>          UNION ALL
>
>          SELECT t.sample_number
>          FROM sample t
>          JOIN recursetree rt ON rt.sample_number = t.parent_sample
>        )
>
>        SELECT sample_number
>        FROM recursetree;
> $BODY$
>  LANGUAGE sql VOLATILE


CREATE OR REPLACE FUNCTION fn_get_subsamples(IN sample_numbers integer[])
 RETURNS SETOF integer AS
$BODY$
       WITH RECURSIVE recursetree(sample_number) AS (
         SELECT sample_number
         FROM sample
         WHERE parent_sample in (select unnest($1))

         UNION ALL

         SELECT t.sample_number
         FROM sample t
         JOIN recursetree rt ON rt.sample_number = t.parent_sample
       )

       SELECT sample_number
       FROM recursetree;
$BODY$
 LANGUAGE sql VOLATILE

merlin

i guess an array is also ok. must been something like:
fn_get_subsamples(IN sample_numbers[] integer) ??

how would a query then look like?

SELECT * FROM sample WHERE sample_number IN ( fn_get_subsamples(1,2,3,4)
) ??

cheers sven



Am 23.03.2011 17:42, schrieb Tom Lane:
> "Sven Haag"<sven-haag@gmx.de>  writes:
>> hello pgsql fans out there,
>> i've already created a function that returns a list of IDs of all sub-samples based on a given sample ID. this works
fine.now i like to extend this function so that it can receive a list of sample IDs. e.g.: 
>> fn_get_subsamples(IN sample_numbers SETOF integer)
> There's no such animal as a function that accepts a set.  You could pass
> it an array of integers instead.  Or maybe just call it more than once.
>
>             regards, tom lane
>

thanks a lot!
that function does it exactly as wished ;)!


Am 23.03.2011 22:46, schrieb Merlin Moncure:
> On Wed, Mar 23, 2011 at 10:29 AM, Sven Haag<sven-haag@gmx.de>  wrote:
>> hello pgsql fans out there,
>>
>> i've already created a function that returns a list of IDs of all sub-samples based on a given sample ID. this works
fine.now i like to extend this function so that it can receive a list of sample IDs. e.g.: 
>>
>> fn_get_subsamples(IN sample_numbers SETOF integer)
>>
>>
>> here is the existing function:
>>
>> CREATE OR REPLACE FUNCTION fn_get_subsamples(IN sample_number integer)
>>   RETURNS SETOF integer AS
>> $BODY$
>>         WITH RECURSIVE recursetree(sample_number) AS (
>>           SELECT sample_number
>>           FROM sample
>>           WHERE parent_sample = $1
>>
>>           UNION ALL
>>
>>           SELECT t.sample_number
>>           FROM sample t
>>           JOIN recursetree rt ON rt.sample_number = t.parent_sample
>>         )
>>
>>         SELECT sample_number
>>         FROM recursetree;
>> $BODY$
>>   LANGUAGE sql VOLATILE
>
> CREATE OR REPLACE FUNCTION fn_get_subsamples(IN sample_numbers integer[])
>   RETURNS SETOF integer AS
> $BODY$
>         WITH RECURSIVE recursetree(sample_number) AS (
>           SELECT sample_number
>           FROM sample
>           WHERE parent_sample in (select unnest($1))
>
>           UNION ALL
>
>           SELECT t.sample_number
>           FROM sample t
>           JOIN recursetree rt ON rt.sample_number = t.parent_sample
>         )
>
>         SELECT sample_number
>         FROM recursetree;
> $BODY$
>   LANGUAGE sql VOLATILE
>
> merlin
>