FAQ
Hi all -

I need to select the rows of a table that satisfy a condition that
includes a function call, and I want to include the result of the
function call as a column in the resulting table. For example, this
pseudo-code produces the result I'm looking for:

SELECT field1, some_function(...) FROM my_table
WHERE some_function(...) < some_number

However, I would like to avoid calling the function in two places,
especially since embedded in the parameters to the function I have
another SELECT command. Not to mention it just looks messy...

I've already tried the following without luck:

SELECT field1, some_function(...) AS result FROM my_table
WHERE result < some_number

Is there a way to cache the result of the function call so it can be
used in both places without two separate calls?

Thanks in advance,
Dallas Morisette

Search Discussions

  • Dallas Morisette at May 17, 2009 at 2:13 pm
    Hi Bastiaan -

    Both the WHERE and HAVING clauses filter the result set, but the
    HAVING clause is, I believe, for cases where you wish to filter on
    aggregate results, like sum or count, while the WHERE clause is for
    non-aggregated results. I'm not using an aggregating function, so I
    believe I need a solution that uses a WHERE clause.

    Thanks for your suggestion.
    Dallas
    On May 16, 2009, at 11:44 PM, Bastiaan Olij wrote:

    Hi Dallas,

    Haven't tried this with functions so I don't know the exact syntax but
    try adding a having clause. Having allows you to filter on your result
    set. Obviously there are some performance limitations but with a
    function you probably have that already.

    Hope that helps,

    Bastiaan Olij

    Dallas Morisette wrote:
    Hi all -

    I need to select the rows of a table that satisfy a condition that
    includes a function call, and I want to include the result of the
    function call as a column in the resulting table. For example, this
    pseudo-code produces the result I'm looking for:

    SELECT field1, some_function(...) FROM my_table
    WHERE some_function(...) < some_number

    However, I would like to avoid calling the function in two places,
    especially since embedded in the parameters to the function I have
    another SELECT command. Not to mention it just looks messy...

    I've already tried the following without luck:

    SELECT field1, some_function(...) AS result FROM my_table
    WHERE result < some_number

    Is there a way to cache the result of the function call so it can be
    used in both places without two separate calls?

    Thanks in advance,
    Dallas Morisette
  • Dallas Morisette at May 17, 2009 at 2:16 pm
    Thanks, Adam. That worked, and makes some sense as well. Basically
    create a temporary table with the function run on all elements
    including the result of the function as a new field, then filter based
    on that added column. If I'm limiting the set based on other
    conditions I can put them in the sub-select statement to minimize the
    number of function calls necessary.

    Regards.
    Dallas

    On May 17, 2009, at 4:56 AM, Adam Ruth wrote:

    You can put the query into a sub-select:

    SELECT * from (SELECT field1, some_function() as field2 FROM
    my_table) T where T.field2 < some_number



    On 17/05/2009, at 10:44 AM, Dallas Morisette wrote:

    Hi all -

    I need to select the rows of a table that satisfy a condition that
    includes a function call, and I want to include the result of the
    function call as a column in the resulting table. For example, this
    pseudo-code produces the result I'm looking for:

    SELECT field1, some_function(...) FROM my_table
    WHERE some_function(...) < some_number

    However, I would like to avoid calling the function in two places,
    especially since embedded in the parameters to the function I have
    another SELECT command. Not to mention it just looks messy...

    I've already tried the following without luck:

    SELECT field1, some_function(...) AS result FROM my_table
    WHERE result < some_number

    Is there a way to cache the result of the function call so it can
    be used in both places without two separate calls?

    Thanks in advance,
    Dallas Morisette

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 17, '09 at 12:44a
activeMay 17, '09 at 2:16p
posts3
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Dallas Morisette: 3 posts

People

Translate

site design / logo © 2021 Grokbase