Q: How do I return differnt rows depending on values in a PL/pgSQL function? - Mailing list pgsql-general

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!