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
