Обсуждение: PL/pgSQL: SELECT INTO only if result count = 1

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

=======

CREATE TABLE town
(
  country_fk character varying(3) NOT NULL,
  region_fk character varying(3) NOT NULL,
  id serial NOT NULL,
  "name" character varying(60) NOT NULL,
  CONSTRAINT ...
)
--

Best Regards,
Tarlika Elisabeth Schmitz

On Sat, 27 Aug 2011 18:45:42 -0300
Osvaldo Kussama <osvaldo.kussama@gmail.com> wrote:

>2011/8/27, Tarlika Elisabeth Schmitz
><postgresql6@numerixtechnology.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

Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de> writes:
> 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

Yeah.  By default, SELECT INTO just fetches one row and stops;
it doesn't look to see if there are more.

You could possibly use SELECT INTO STRICT and catch the error if
there's more than one row.  I suspect though that it'd be more efficient
to use a FOR loop and just note for yourself how many rows you get.

            regards, tom lane

On Sat, 27 Aug 2011 18:36:14 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

>Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de> writes:
>> 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
>
>Yeah.  By default, SELECT INTO just fetches one row and stops;
>it doesn't look to see if there are more.
>
>You could possibly use SELECT INTO STRICT and catch the error if
>there's more than one row.

What a life saver late on a Saturday night! That does the trick.

--

Best Regards,
Tarlika Elisabeth Schmitz