My server is Postgres 8.0.1 on fedora core2. My clients are remote and interface with my server using .NET GUI.
We are trying to store many images/icons/audio/video clips in our system. We expect many of these. The way we are doing it is using Hierarchical File System.
I understand we need to limit the size of these directories by controlling number of resources in each directory.
The way i want to let anone access these resources is only through stored procedures rather than direct downloading. I am trying to create stored procedure/function API for the same. These functions will check for the permissions for the user and/or the file, check the location from the schema and then would "stream" it out for the GUI to use.
The question is: (1) How do i use the database stored procedures/functions as a tunnel for just streaming the data rather than storing it in database? That is, a function that given a Image ID for example, will read the location from the table and then just go to the location and stream out the bits. (2) Am i right in saying that it can't solely be done iwth plpgsql but would need somem c/c++ api. (3) Has anyone here done something like this and can share with me how he/she implemented this.
I did do my homework of googling for something like this but may be my search skills were not strong enough to find some substantial information/HOW TOs or examples.