Skip site navigation (1) Skip section navigation (2)

how to create a function in plpgsql which picks the name of a variable which is NULL out of a list of variables?

From: Miernik <public(at)public(dot)miernik(dot)name>
To: pgsql-novice(at)postgresql(dot)org
Subject: how to create a function in plpgsql which picks the name of a variable which is NULL out of a list of variables?
Date: 2008-07-04 07:17:11
Message-ID: 20080704071711.1D1A.0.NOFFLE@debian107.local (view raw or flat)
Thread:
Lists: pgsql-novice
Hello. I am trying to create a function in plpgsql, but I got stuck on
one issue, looking for some clues.

Inside the function I have 4 variables of type boolean:

  a1 boolean;
  a2 boolean;
  a3 boolean;
  a4 boolean;

Their values can be TRUE, FALSE or NULL;

Now I need this function to return the name of a randomly picked
NULL-valued variable out of these four, or return NULL in case none of
the 4 variables are NULL.

So if for example a1 is TRUE, a2 and a3 are NULL and a4 is FALSE, then
the function should return string 'a2' or 'a3' (randomly picked).

I written this part, but looking for any clues how to proceed:



CREATE OR REPLACE FUNCTION pick_random_a(thisid integer) RETURNS varchar(2) AS $BODY$
DECLARE
  a1 boolean;
  a2 boolean;
  a3 boolean;
  a4 boolean;
  this_a varchar(2);
BEGIN
  SELECT va1, va2, va3, va4 INTO a1, a2, a3, a4 FROM tmp_a WHERE id = thisid;


--
-- so here should be some block which gives the variable this_a
-- a string or NULL value, how to do it?
--


  RETURN this_a;
END;
$BODY$ LANGUAGE plpgsql;




-- 
Miernik
http://miernik.name/


pgsql-novice by date

Next:From: Emil ObermayrDate: 2008-07-07 08:18:02
Subject: how to get dependancies of a table?
Previous:From: Frank BaxDate: 2008-07-03 10:27:52
Subject: Re: date formatting question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group