On 11/19/2014 08:26 AM, Anil Menon wrote:
Hello,
I would like to ask from your experience which would be the best
"generic" method for checking if row sets of a certain condition exists
in a PLPGSQL function.
I know of 4 methods so far (please feel free to add if I missed out any
others)
1) get a count (my previous experience with ORCL shaped this option)
select count(*) into vcnt
from table
where <<condition>>
if vcnt >0 then
do X
else
do y
end if
Cons : It seems doing a count(*) is not the best option for PG
Well that would depend on the table size, whether it was 100 rows vs
1,000,000 rows
2) Use a non-count option
select primary_key_Col into vcnt
from table
where <<condition>>
if found then
do X
else
do y
end if
Cons :Some people seems not to prefer this as (AFAIU) it causes a
plpgsql->sql->plpgsql switches
plpgsql is fairly tightly coupled to SQL, so I have not really seen any
problems. But then I am not working on large datasets.
3) using perform
perform primary_key_Col into vcnt
from table
where <<condition>>
if found then
do X
else
do y
end if
Seems to remove the above (item 2) issues (if any)
AFAIK, you cannot do the above as written. PERFORM does not return a result:
http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
It would have to be more like:
perform primary_key_Col from table where <<condition>>
4) using exists
if exists ( select 1 from table where <<condition>> ) then
do x
else
do y
end if
My question is what would be the best (in terms of performance) method
to use? My gut feeling is to use option 4 for PG. Am I right or is there
any other method?
All of the above is context specific. To know for sure you will need to
test on actual data.
Thanks in advance
Anil
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
