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

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!

Max Ahston wrote:
> 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

Couple problems that I can see. First, that test should be:
   IF $2 = ''tigris'' then
Second, you'll need to add a "return;" line after the "END IF" for the
cases where there is no match. In fact, you can just move the one from
within the IF...END IF to outside it.

HTH,

Joe

> Couple problems that I can see. First, that test should be:
>    IF $2 = ''tigris'' then
Thank you.

> Second, you'll need to add a "return;" line after the "END IF" for the
> cases where there is no match. In fact, you can just move the one from
> within the IF...END IF to outside it.
Ok, that explained an error I saw, but the function wasn't finished. This
is how the final result looks:

create or replace function get_dns2(varchar(40), varchar(40)) returns
setof holder as
'
declare
    r holder%rowtype;
begin
       IF ($2 = ''tigris'') then
          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;
       ELSE
          for r in select Attribute, op, value FROM radreply
             WHERE username = $1 loop
             return next r;
          end loop;
       END IF;
       return;
end
'
language 'plpgsql';


Thanks for the help!

Max!