Re: Custom function in where clause

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 10 July 2012 18:36, Pena Kupen <kupen@xxxxxxxxxx> wrote:
> Hi,
>
> I have searched solution to my problem a few days. On my query, there is big
> performance problem.
> It seems to me, that problem is on where-part of sql and it's function.
>

> How should I handle this situation and use function?
>

I would start by rewriting your function in plain SQL rather than
PL/pgSQL.  As a general rule, don't write a function in PL/pgSQL
unless you really need procedurality.  This function does not.

For example:

CREATE OR REPLACE FUNCTION get_level_value(_user integer, _id, _area)
RETURNS integer
   AS $$
  -- Return 3 if there are matching records in table_o, otherwise return 1.
  SELECT CASE WHEN EXISTS (
         SELECT id
         FROM table_o
         WHERE userid=_user AND
               id=_id AND
               area=_area
  ) THEN 3 ELSE 1 END;
$$
LANGUAGE sql STABLE;

Cheers,
BJ

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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux