To return a result set use SETOF, like so CREATE FUNCTION test() RETURNS SETOF text AS '
To allow access to the tables only through a function, take a look at declaring your functions with SECURITY DEFINER CREATE FUNCTION test() RETURNS SETOF text SECURITY DEFINER AS ' .. Inside the function you will now have permissions of the user that created the function. See here for details http://www.postgresql.org/docs/7.4/interactive/sql-createfunction.html Also this may be useful http://techdocs.postgresql.org/guides/SetReturningFunctions HTH adam > Hello, > I have following problem: > > A user "xy" shouldn't have any rights to a table, > but needs data from the content of the table. > My idea was to setup a PL/PGSQL procedure to fetch the > data from the table, so that the user only is allowed to > access the procedure. I also tried using a SQL function, > but this doesn't work, too. > Working with views may be a solution - or are e.g. cursors > in pl/pgsql the solution ?? > The problem i run into is, that although i can read the data > and return it, I can not return more than one row each > function call. Is it possible to return a whole resultset ? > > My last try was: > > drop function test(int); > create function test(int) returns table_name as ' > select * from table_name where column_name1>= $1 > order by column_name1; > ' language sql; > select * from test(1) ; > > The pl/pgsql variant: > > > drop function test(); > CREATE FUNCTION test() RETURNS text AS ' > declare > target table_name%ROWTYPE; > begin > select * into target from table_name ; > return target.column_name1 || target.column_name2; > end; > ' LANGUAGE plpgsql; > select test(); > But in PL/pgsql i am not able to return a cursor or something like this > and I am not able to return more than one row. > > So i have got 2 maybe solutions, but none of them works. > > Has anyone a hint, how to "hide" original tables and making their data > selectable to some users ? The result really should be a > select a.* , b.* from a,b where a.state!="imgonewild" .... > > Thanks in advance, > Andre > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
