Say I have defined the data-structure:
Users(u_id, name);
As well as a stored-procedure / function to get a full profile of the user, given the u_id:
getUserProfile(u_id) RETURNS (u_id, firstname, lastname, age);
Now, I want to have a way to easily retrieve all users, say under the age of 20. What would be the appropriate component to build on top of this, so that I could call something like:
SELECT *
FROM user_profiles as UP
WHERE UP.age < '20'
Assuming getUserProfile() returns a custom data type with the mentioned columns, you can do the following:
SELECT (prof).u_id,
(prof).firstname,
(prof).lastname,
(prof).age
FROM (
SELECT getUserProfile(u_id) as prof
FROM users
) t
WHERE (prof).age < 20
Note that the column prof needs to be enclosed in brackets, otherwise the parser will think it's a table reference.
You can wrap the whole thing into a view (apparently without the WHERE condition) to make things easier.
you need another stored-procedure / function to get the users under the age of 20 and for each user this first proc or function returns, you can call the second.
Or you can do everything in one structure. Create a procedure that returns users' information and that accept another parameter called age and then you return a result set instead of only one user.
firstname, lastname, agecome from? What is a "data-structure" supposed to be? The functiongetUserProfile()is obviously specialized in returning the details for one user. You need to query the source table.getUserProfile()function executes some procedural-language function, in fact PL/Java. But, what I really like is to have some way of querying user-profiles, similar to if it was a data-set. I.e. Finding all my users who are of a particular age?getUserProfile()function asreturns tableyou can use it like a view:select * from getUserProfile() where age < 20of course in that case you would not pass a u_id to it.