PL/pgSQL: SELECT INTO variables - no result

On Thu, 10 Nov 2011 17:46:47 -0500
"David Johnston" <polobo@yahoo.com> wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tarlika
Elisabeth Schmitz
Sent: Thursday, November 10, 2011 5:18 PM
To: pgsql-general@postgresql.org>
Subject: [GENERAL] PL/pgSQL: SELECT INTO variables - no result

I go through a series of SELECT INTO in a trigger function.

SELECT INTO country_id id
FROM vcountry WHERE [...];

I might or o might not find a result.

Next, I try to find a region within this country, if found previously,
and if not anywhere.

SELECT INTO
country_id, region_id
country_fk, id
FROM vregion
WHERE (country_id IS NULL OR country_fk = country_id) AND name ILIKE
place;

If the first search found a country, and the second search fails to
find the region, the second search will set my variables to NULL. So,
a previously populated country_id will now be NULL.

Is there any way I can avoid this or do I have to use different sets of
variables?

------------------------------------

Don't return the "country_fk" field in your SELECT list, just the
[region_]id

If the country search returns no results and the region search does,
then I get the country_id via the region.

If zero results nulls the variables, I suppose I have to use two sets
of variables.