Re: PL/pgSQL: SELECT INTO only if result count = 1

On Sat, 27 Aug 2011 18:45:42 -0300
Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com> wrote:

>2011/8/27, Tarlika Elisabeth Schmitz
><postgresql6(at)numerixtechnology(dot)de>:
>> Hello,
>>
>> In a PL/pgSQL trigger function, I try to select a record from table
>> "town" below.
>>
>> I am only interested in the result if the returned result set
>> contains exactly one result.
>> If there is more than one result, I want to log the fact.
>>
>> EXAMPLE pseudo code
>>
>> select
>> country_fk, region_fk, id
>> from town
>> where name = 'Newcastle'
>>
>> if found and count = 1
>> populate country_id, region_id, town_id
>> else
>> raise notice 'ambiguous %', town.name
>>
>>[...]
>> --
>
>
>Use:
>GET DIAGNOSTICS integer_var = ROW_COUNT;

SELECT INTO
country_id, region_id, town_id
country_fk, region_fk, id
FROM town
WHERE ...;

GET DIAGNOSTICS cnt = ROW_COUNT;
RAISE DEBUG 'COUNT %', cnt;

always returns 1

--

Best Regards,
Tarlika Elisabeth Schmitz