From: | Brendan Jurd <direvus(at)gmail(dot)com> |
---|---|
To: | Pena Kupen <kupen(at)wippies(dot)fi> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Custom function in where clause |
Date: | 2012-07-10 08:45:29 |
Message-ID: | CADxJZo0NNM0u6197HoyemXT58sQgZPTN1oM-q+S92jgO7r=JRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 10 July 2012 18:36, Pena Kupen <kupen(at)wippies(dot)fi> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Pena Kupen | 2012-07-10 09:30:38 | Re: Custom function in where clause |
Previous Message | Maxim Boguk | 2012-07-10 08:44:18 | Re: Custom function in where clause |