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

From: Tarlika Elisabeth Schmitz <postgresql6(at)numerixtechnology(dot)de>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: PL/pgSQL: SELECT INTO only if result count = 1
Date: 2011-08-27 20:14:21
Message-ID: 20110827211421.320e4c70@dick.coachhouse
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kenneth McDonald 2011-08-27 20:27:46 Still no way to install PostGres on os x Lion?
Previous Message Jerry Sievers 2011-08-27 19:13:04 Re: Feature request: per user connections limit