Can you filter a recordset once it has been created?

[gonsman]

Let's see if I can explain this clearly. I am creating a function that checks permissions for selects, inserts, updates/deletes. The permissions are stored in a table with the users info. When a user tries to perform a certain action, I call the function to make sure that user is allowed to perform that action. In the cases when a select is called, I want to return only the results that the user is allowed to see (what they have permissions to). I could add to each select statement the required SQL, but it is too confusing for some other programmers on our team to have to do this and it makes the SQL much less readable and user-friendly.

What I want to do is select all of the rows (normally) and then send the record set to a function that "filters" out the rows which the user is not allowed to see. So far, the only way I can do this is to create a new array with the desired rows in it and have the function return that array. If this doesn't seem like a problem, then I will go this route. I just thought it would be nicer if I could leave the results as a recordset and not a PHP array (I understand that they are different). Are there performance differences, I am not sure?

-Sean

[tomhath]

You could probably hide the additional SQL required by creating a VIEW, then use the view name in place of the table name in the FROM.

[gonsman]

Can you use a view if the table is table is different for each section (jobs, news, press releases). I am not familiar with views.

[tomhath]

Whether or not a VIEW will work for you depends on the database your are using. Here's a link that discusses views pretty well.

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/28863

Briefly, in your SELECT statements you replace the name of a table with the name of a view (SQL syntax is exactly the same, a view is sometimes called a virtual table).

Here's a silly example. Suppose you have a table named someTable with a column called ID. You could select all the rows that have ID > 100 with this:

select * from someTable where ID > 100

Or you could define this view:

create view someView as select * from someTable where ID > 100

Now you would select the same rows as above with this:

select * from someView

This example is silly because the "select ..." statement that created the view is so simple; but in your case, you could hide the additional logic that restricts what the user is allowed to see in the "create view" with a more complicated select.

[gonsman]

I am using postgreSQL and I am pretty sure I can do views, but I think I am going to pass the sql to a function and filter the results and return as an array. I just don't see how views would work because many different things have to be checked. I would have to have a different view for every table. If I use a function, no one has to create a view they just pass a normal sql string to the function and automatically get the filtered results. If there are many results returned this could be become an serious overhead, so I was hoping to create a recordset instead.

-Sean