PDA

View Full Version : Stored procedure - sub query question


lux
01-09-2007, 10:11 AM
Hey, does anyone know much about plpgsql?

I am trying to write a stored procedure that will be passed a set of arguments that relate to a list of coloumns in one table. Where the strings passed equal a single row the value in a final column will be used to identify a column name in a second table, and this will be tested to return a boolean value of true or false.

So far i have created this:

CREATE OR REPLACE FUNCTION acl(int, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar) RETURNS boolean AS '


select (
select valuecolindex from tbl_acl
where tbl_acl.p1= $2
and tbl_acl.p2= $3
and tbl_acl.p3= $4
and tbl_acl.p4= $5
and tbl_acl.p5= $6
and tbl_acl.p6= $7
and tbl_acl.p7= $8
and tbl_acl.p8= $9
) from tbl_user
where userid = $1;
' LANGUAGE sql;


but it only returns the value in the final column in the first table (tbl_acl),

does anyone have a suggestion as to how to correctly write my sub query to work properly or could i some how use plpgsql to get it to work?

lux
01-09-2007, 01:42 PM
done it, can't imagine anyone will want to see the answer so i won't post it

Andromeda
01-09-2007, 06:48 PM
I for one wouldn't understand the answer even if you did post it! but I'm glad you got it sorted.

domedia
01-09-2007, 06:57 PM
done it, can't imagine anyone will want to see the answer so i won't post it If anyone stumbles upon this question in the future, it's a pretty good chance they're interested.

lux
01-10-2007, 09:26 AM
ok so; the aim was to find out the value of a column for one row, where that rows other columns matched the values passed to it in the function. Then use the value to find the corresponding column name in a user table, matching the user id from a value passed in the function inside a postgresql db:


CREATE OR REPLACE FUNCTION function(int, varchar,
varchar, varchar, varchar, varchar, varchar, varchar, varchar,) RETURNS boolean AS '

DECLARE
colname text;
querystr text;
retval boolean;
crs refcursor;

BEGIN

select into colname valuecolindex from tbl_acl
where p1 = $2
and p2 = $3
and p3 = $4
and p4 = $5
and p5 = $6
and p6 = $7
and p7 = $8
and p8 = $9;

querystr := 'select (' || colname || ') from tbl_user where userid = ' || $1;

open crs for execute querystr;
fetch crs into retval;
close crs;

return retval;

END; '
LANGUAGE plpgsql