Thread: calling a function over several rows

Hello,
There is an existing function which takes an integer and returns a
record.  I need to call this function with every integer in a table.  Is
there a simple shortcut for doing this?

I'm looking for something like:

select f.*
from function(t.value) f, table t

Thanks,
Adam

On Tue, Nov 17, 2009 at 1:02 AM, Adam Rich <adam.r@sbcglobal.net> wrote:
> Hello,
> There is an existing function which takes an integer and returns a record.
>  I need to call this function with every integer in a table.  Is there a
> simple shortcut for doing this?
>
> I'm looking for something like:
>
> select f.*
> from function(t.value) f, table t

select (f).* from (select function(t.value) as f from table t) q;

merlin

2009/11/17 Adam Rich <adam.r@sbcglobal.net>:
> Hello,
> There is an existing function which takes an integer and returns a record.
>  I need to call this function with every integer in a table.  Is there a
> simple shortcut for doing this?
>
> I'm looking for something like:
>
> select f.*
> from function(t.value) f, table t
>

Hello,

attention, relation databases isn't spreadsheet. There isn't any
shortcut. You can write function based on information_schema.colums
that helps with generating SQL statement.

Regards
Pavel Stehule

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

Merlin Moncure wrote:
> On Tue, Nov 17, 2009 at 1:02 AM, Adam Rich <adam.r@sbcglobal.net> wrote:
>> Hello,
>> There is an existing function which takes an integer and returns a record.
>>  I need to call this function with every integer in a table.  Is there a
>> simple shortcut for doing this?
>>
>> I'm looking for something like:
>>
>> select f.*
>> from function(t.value) f, table t
>
> select (f).* from (select function(t.value) as f from table t) q;
>
> merlin
>

Thanks, that's perfect, and much faster than the one I came up with in
the interim:

select (f(t.value)).* from table t;

On Tue, Nov 17, 2009 at 1:35 AM, Adam Rich <adam.r@sbcglobal.net> wrote:
> Merlin Moncure wrote:
>>
>> On Tue, Nov 17, 2009 at 1:02 AM, Adam Rich <adam.r@sbcglobal.net> wrote:
>>>
>>> Hello,
>>> There is an existing function which takes an integer and returns a
>>> record.
>>>  I need to call this function with every integer in a table.  Is there a
>>> simple shortcut for doing this?
>>>
>>> I'm looking for something like:
>>>
>>> select f.*
>>> from function(t.value) f, table t
>>
>> select (f).* from (select function(t.value) as f from table t) q;
>>
>> merlin
>>
>
> Thanks, that's perfect, and much faster than the one I came up with in the
> interim:
>
> select (f(t.value)).* from table t;

Your version is basically correct. It's slower because of a nasty
gotcha that is stemming from the fact that ().* is not really an
operator.  It is expanded during parsing so that for each field of
your type, the function is run, like this:

select (f(t.value)).* from table t;
select f(t.value).a, f(t.value).b, f(t.value).c from table t;

I've griped about this a few times, but I don't know if it's fixable
or even broken...it's just something you have to watch out for.

merlin