[GENERAL] Q: How do I return differnt rows depending on values in a PL/pgSQL function?

[Max Ahston]

Hi,

I am pretty new to pgsql but have the basic knowledge of sql. I am trying 
to figure out how to solve the following with a funtion:

I want to run a function (I guess written in pl/pgsql) that takes two 
variables (username and nasname).

Depending on boxname I want two different results.

radiusdb=# select * from radreply;
 id | username |          attribute          | op |  value   
----+----------+-----------------------------+----+----------
  1 | test     | Ascend-Client-Primary-DNS   | := | 10.0.0.1
  2 | test     | Ascend-Client-Secondary-DNS | := | 10.0.0.2

I've created a handler,

create type holder as (Attribute VARCHAR(30), op varchar(2), Value 
varchar(40));


This is the function I've managed to come up with by reading documentation 
and testing. It's r e a l l y basic, I know :)

create or replace function get_dns2(varchar(40), varchar(40)) returns 
setof holder as 
'
declare
    r holder%rowtype;
begin
       for r in select \'Acc-Dns-Server-Pri\', op, value FROM radreply  
       where username = $1 and attribute = \'Ascend-Client-Primary-DNS\' 
loop
           return next r;
       end loop;

       for r in select \'Acc-Dns-Server-Sec\', op, value FROM radreply  
       where username = $1 and attribute = \'Ascend-Client-Secondary-DNS\' 
loop
           return next r;
       end loop;

       return;
end
' 
language 'plpgsql';



Now I want to insert a IF check that matches $2 against the value 
'tigris'. If there is a match, the code should run, if not.. it will 
return the matching rows without anything else.

Below is a test of the function with the IF statement added.

create or replace function get_dns(varchar(40), varchar(40)) returns 
setof holder as 
'
declare
    r holder%rowtype;
begin
    IF ($2 == "tigris") then
       for r in select Attribute, op, value FROM radreply 
       WHERE username = $1 loop
           return next r;
       end loop;
       return;
    END IF;
end
' 
language 'plpgsql';

Returns the following:

ERROR:  column "tigris" does not exist
CONTEXT:  PL/pgSQL function "get_dns2" line 4 at if

So it's trying to match against some column, not what I wanted, and 
doesn't work that well.. 

How do I get my IF statement to work? 

You might wonder why I'm bothering with this "rewrite" thing. I could 
always add another column with a value, 1 for tigris example, and put the 
different values directly in the table. That would give 4 rows / user. And 
Since there might be as much as 400k+ users that means I could get as many 
as 1.6m rows instead of 800k. 

Of course I need to try out different scenarios, I need to run 2 queries 
instead of one for example and so on.. But.... I guess it will show once I 
understand better how to build the functions I belive I need.

Thanks in advance,

Max!


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster