Thread: passing a temporary table with more than one column to a stored procedure

Hi,

is there a way to pass a temporary result table with more than column
to a stored procedure?

Specifically, I would like to run a variation of this query:

SELECT
    *
FROM
    graphOverTokens(
        (
            SELECT 1 AS min, 10 AS max, 2 AS text_ref
            UNTION SELECT 20 AS min, 30 AS max, 3 AS text_ref
        ) AS boundaries
        JOIN struct ON (struct.text_ref = boundaries.text_ref)
        JOIN rank ON (rank.struct_ref = struct.id)
    ) AS graph

I see two problems:

a) I get an error message "subquery must return only one column" when
I pass a table with more than one column as an argument to my function

b) I would have to create a complex data type of the same schema as my
argument table (a join of boundaries, struct, graph).  This is not a
problem in the example above, however I would like to use a similar
function in queries where the schema of the input relation is not fixed.

Thanks,
Viktor

> is there a way to pass a temporary result table with more than column
> to a stored procedure?
>
> Specifically, I would like to run a variation of this query:
>
> SELECT
>     *
> FROM
>     graphOverTokens(
>         (
>             SELECT 1 AS min, 10 AS max, 2 AS text_ref
>             UNTION SELECT 20 AS min, 30 AS max, 3 AS
text_ref
>         ) AS boundaries
>         JOIN struct ON (struct.text_ref = boundaries.text_ref)
>         JOIN rank ON (rank.struct_ref = struct.id)
>     ) AS graph
>

What does the signature of graphovertokens look like?  Three parmaters
and it doesn't return a setof?


select graphovertokens(graph.min, graph.max, graph.text_ref), *
from (
         (
             SELECT 1 AS min, 10 AS max, 2 AS text_ref
             UNTION SELECT 20 AS min, 30 AS max, 3 AS
text_ref
         ) AS boundaries
         JOIN struct ON (struct.text_ref = boundaries.text_ref)
         JOIN rank ON (rank.struct_ref = struct.id)
     ) AS graph



Jon

Hi Jon,

Am 28.04.2008 um 19:23 schrieb Roberts, Jon:
>> What does the signature of graphovertokens look like?  Three
>> parmaters
> and it doesn't return a setof?

This is my problem.  The return type is setof something (doesn't
really matter), but I don't know what to put into the argument list.

Any ideas?

Viktor

> -----Original Message-----
> From: Viktor Rosenfeld [mailto:rosenfel@informatik.hu-berlin.de]
> Sent: Monday, April 28, 2008 4:52 PM
> To: Roberts, Jon
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] passing a temporary table with more than one
column
> to a stored procedure
>
> Hi Jon,
>
> Am 28.04.2008 um 19:23 schrieb Roberts, Jon:
> >> What does the signature of graphovertokens look like?  Three
> >> parmaters
> > and it doesn't return a setof?
>
> This is my problem.  The return type is setof something (doesn't
> really matter), but I don't know what to put into the argument list.
>
> Any ideas?
>
You could pass in arrays to the function.

A less efficient way would be to create a temp table, loop through the
main result set and call your function one record at a time.  Take the
results from each execution of the function and insert that into the
temp table.  Then return the values from the temp table.


Jon

It looks like you need an aggregate function... but aggregate would
work in case, you want to return a RECORD and not a SETOF RECORD.

In this case, you probably need to operate with arrays. Are you on
8.3? If yes, you would be able to pass an array of type to your
function.

You can accumulate your type-array with array_accum(anyelement)
aggregate (http://www.postgresql.org/docs/8.3/static/xaggr.html)...
but I am not sure about the memory consumption in case of large arrays
being passed to the function.

If you are in the 8.2, you can still convert any type to text, and
deconvert in the function: textin(point_out('(1,1)'::point))

With best regards,

-- Valentine


On Apr 28, 11:52 pm, rosen...@informatik.hu-berlin.de (Viktor
Rosenfeld) wrote:
> Hi Jon,
>
> Am 28.04.2008 um 19:23 schrieb Roberts, Jon:
>
> >> What does the signature of graphovertokens look like?  Three
> >> parmaters
> > and it doesn't return a setof?
>
> This is my problem.  The return type is setof something (doesn't
> really matter), but I don't know what to put into the argument list.
>
> Any ideas?
>
> Viktor
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Viktor

The quick and dirty method would be to pass the subquery as a string,
then execute the subquery in the function.

Will T

Hi Valentine,

a little experimentation indicates that an aggregate function can
solve my problem, using an int[] array as the state variable to encode
the computed tuples of the result table so far and then using a costum
function to decode the final returned array from the aggregate into
the table I'm looking for.

I'm afraid though that the SQL aggregate semantics (like having to use
GROUP BY and so on) will get in my way.

I'l let you know,
VIktor

Am 29.04.2008 um 10:30 schrieb valgog:
> It looks like you need an aggregate function... but aggregate would
> work in case, you want to return a RECORD and not a SETOF RECORD.
>
> In this case, you probably need to operate with arrays. Are you on
> 8.3? If yes, you would be able to pass an array of type to your
> function.
>
> You can accumulate your type-array with array_accum(anyelement)
> aggregate (http://www.postgresql.org/docs/8.3/static/xaggr.html)...
> but I am not sure about the memory consumption in case of large arrays
> being passed to the function.
>
> If you are in the 8.2, you can still convert any type to text, and
> deconvert in the function: textin(point_out('(1,1)'::point))
>
> With best regards,
>
> -- Valentine
>
>
> On Apr 28, 11:52 pm, rosen...@informatik.hu-berlin.de (Viktor
> Rosenfeld) wrote:
>> Hi Jon,
>>
>> Am 28.04.2008 um 19:23 schrieb Roberts, Jon:
>>
>>>> What does the signature of graphovertokens look like?  Three
>>>> parmaters
>>> and it doesn't return a setof?
>>
>> This is my problem.  The return type is setof something (doesn't
>> really matter), but I don't know what to put into the argument list.
>>
>> Any ideas?
>>
>> Viktor
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
>> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general