Re: PL/pgSQL: SELECT INTO variables - no result

On Thu, 10 Nov 2011 17:46:47 -0500
"David Johnston" <polobo(at)yahoo(dot)com> wrote:

>-----Original Message-----
>From: pgsql-general-owner(at)postgresql(dot)org
>[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tarlika
>Elisabeth Schmitz
>Sent: Thursday, November 10, 2011 5:18 PM
>To: pgsql-general(at)postgresql(dot)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.